pg.ddx.io  pgsql-docs@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Foreign key creation
2+ messages / 2 participants
[nested] [flat]

* Foreign key creation
@ 2001-04-21 15:14  edp <edp@elgi.jet.co.in>
  0 siblings, 1 reply; 2+ messages in thread

From: edp @ 2001-04-21 15:14 UTC (permalink / raw)
  To: pgsql-docs

How to create foreign key in Posgresql.
 
regards 
murali

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Foreign key creation
@ 2001-04-22 11:41  Oliver Elphick <olly@lfix.co.uk>
  parent: edp <edp@elgi.jet.co.in>
  0 siblings, 0 replies; 2+ messages in thread

From: Oliver Elphick @ 2001-04-22 11:41 UTC (permalink / raw)
  To: edp <edp@elgi.jet.co.in>; +Cc: pgsql-docs

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 





^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2001-04-22 11:41 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-21 15:14 Foreign key creation edp <edp@elgi.jet.co.in>
2001-04-22 11:41 ` Oliver Elphick <olly@lfix.co.uk>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox