Thread: Foreign key creation

edp wrote:
  >How to create foreign key in Posgresql.

There are two ways, as shown below:

CREATE TABLE supplier (
      id INTEGER PRIMARY KEY,
      name TEXT
);

CREATE TABLE invoice (
      invno SERIAL PRIMARY KEY,
      supplier INTEGER NOT NULL
                       REFERENCES supplier(id)
                         ON UPDATE CASCADE
                         ON DELETE RESTRICT
                         DEFERRABLE,
      ...etc...
);

CREATE TABLE location (
     id CHAR(2) PRIMARY KEY
     name TEXT NOT NULL
);

CREATE TABLE supplier_location (
     supplier INTEGER NOT NULL
                       REFERENCES supplier(id)
                         ON UPDATE CASCADE
                         ON DELETE RESTRICT
                         DEFERRABLE,
     location CHAR(2) NOT NULL
                       REFERENCES location(id)
                         ON UPDATE CASCADE
                         ON DELETE RESTRICT
                         DEFERRABLE
);

CREATE TABLE delivery (
     product VARCHAR(10),
     supplier INTEGER,
     location CHAR(2),
     ...etc...,
     PRIMARY KEY (product, supplier, location),
     FOREIGN KEY (supplier, location) REFERENCES supplier_location
                         ON UPDATE CASCADE
                         ON DELETE RESTRICT
                         MATCH FULL
                         DEFERRABLE
);
--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "If my people, which are called by my name, shall
      humble themselves, and pray, and seek my face, and
      turn from their wicked ways; then will I hear from
      heaven, and will forgive their sin, and will heal
      their land."    II Chronicles 7:14