Mega Code Archive

 

Categories / PostgreSQL / Constraints

 

Create FOREIGN KEY

postgres=# postgres=# CREATE TABLE "books" ( postgres(#      "id"           integer NOT NULL, postgres(#      "title"        text NOT NULL, postgres(#      "author_id"    integer, postgres(#      "subject_id"   integer, postgres(#      Constraint "books_id_pkey" Primary Key ("id") postgres(# ); NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "books_id_pkey" for table "books" CREATE TABLE postgres=# postgres=# CREATE TABLE editions postgres-#               (isbn text, postgres(#               book_id integer, postgres(#               edition integer, postgres(#               publisher_id integer, postgres(#               publication date, postgres(#               type char, postgres(#               CONSTRAINT pkey PRIMARY KEY (isbn), postgres(#               CONSTRAINT integrity CHECK (book_id IS NOT NULL postgres(#                                           AND edition IS NOT NULL), postgres(#               CONSTRAINT book_exists FOREIGN KEY (book_id) postgres(#                          REFERENCES books (id) postgres(#                          ON DELETE CASCADE postgres(#                          ON UPDATE CASCADE); NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "pkey" for table "editions" CREATE TABLE postgres=# postgres=# drop table books cascade; NOTICE:  drop cascades to constraint book_exists on table editions DROP TABLE postgres=# drop table editions cascade; DROP TABLE postgres=#