Skip site navigation (1) Skip section navigation (2)

Re: REFERENCES to foreign tables

From: John DeSoi <desoi(at)pgedit(dot)com>
To: D(dot)C(dot) <coughlandesmond(at)yahoo(dot)fr>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: REFERENCES to foreign tables
Date: 2005-05-21 12:27:33
Message-ID: 776c1e00825485da738a3410a9e0dc31@pgedit.com (view raw or flat)
Thread:
Lists: pgsql-novice
On May 21, 2005, at 8:08 AM, D.C. wrote:

> So when a sale is entered, postgreSQL keeps a record of who it was 
> sold to.  Now what happens if I want to delete the client who bought 
> the item in question ?  I'm going to have a 'sales' table that 
> references a buyer who no longer exists (in db terms, naturally).  I 
> wondered aloud (talking to the wall, sort of) if I could reference 
> clients.client_id to sales.sale_id, but wouldn't that stop my adding a 
> client who hasn't yet made a purchase ?


You need to decide what you want to happen when deleting a client row. 
You can specify this as part of the foreign key declaration with the 
'ON DELETE' specification. You can have the database delete the client 
and sales record or it can be setup to generate an error if this is 
attempted (the default). So using the defaults, you'll be able to 
delete a client with no sales but you'll get an error otherwise.

See the CREATE TABLE documentation for the syntax and all of the 
options.

http://www.postgresql.org/docs/8.0/interactive/sql-createtable.html


John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL


In response to

pgsql-novice by date

Next:From: Michael GlaesemannDate: 2005-05-21 12:36:02
Subject: Re: REFERENCES to foreign tables
Previous:From: D.C.Date: 2005-05-21 12:08:32
Subject: REFERENCES to foreign tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group