Message-Id: <200104221141.f3MBfLU16722@linda.lfix.co.uk>
To: edp <edp@elgi.jet.co.in>
cc: "'pgsql-docs@postgresql.org'" <pgsql-docs@postgresql.org>
Subject: Re: Foreign key creation 
In-reply-to: Message from edp <edp@elgi.jet.co.in> of Sat,
	21 Apr 2001 20:44:58 +0530.
	<73010348951BD511B09E00A0C9ED2F0F08F747@ELGIMAIL>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Date: Sun, 22 Apr 2001 12:41:21 +0100
From: "Oliver Elphick" <olly@lfix.co.uk>

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