Re: REFERENCES to foreign tables

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