1
0

schema.sql 1.1 KB

1234567891011121314151617181920212223242526272829303132333435
  1. CREATE TABLE pilots (
  2. id integer NOT NULL,
  3. name text NOT NULL
  4. );
  5. ALTER TABLE pilots ADD CONSTRAINT pilot_pkey PRIMARY KEY (id);
  6. CREATE TABLE jets (
  7. id integer NOT NULL,
  8. pilot_id integer NOT NULL,
  9. age integer NOT NULL,
  10. name text NOT NULL,
  11. color text NOT NULL
  12. );
  13. ALTER TABLE jets ADD CONSTRAINT jet_pkey PRIMARY KEY (id);
  14. ALTER TABLE jets ADD CONSTRAINT jet_pilots_fkey FOREIGN KEY (pilot_id) REFERENCES pilots(id);
  15. CREATE TABLE languages (
  16. id integer NOT NULL,
  17. language text NOT NULL
  18. );
  19. ALTER TABLE languages ADD CONSTRAINT language_pkey PRIMARY KEY (id);
  20. -- Join table
  21. CREATE TABLE pilot_languages (
  22. pilot_id integer NOT NULL,
  23. language_id integer NOT NULL
  24. );
  25. -- Composite primary key
  26. ALTER TABLE pilot_languages ADD CONSTRAINT pilot_language_pkey PRIMARY KEY (pilot_id, language_id);
  27. ALTER TABLE pilot_languages ADD CONSTRAINT pilot_language_pilots_fkey FOREIGN KEY (pilot_id) REFERENCES pilots(id);
  28. ALTER TABLE pilot_languages ADD CONSTRAINT pilot_language_languages_fkey FOREIGN KEY (language_id) REFERENCES languages(id);