Thread: How to tell PostgreSQL about a relationship

Hi,

I have jumped from MySQL to PostgreSQL, and I wanted to know how we
declare that a table depends on another one.

Currently I have 3 tables:

Product(id, title, price)
Item(id, product_id, order_id, quantity)
Order(id, amount, paid)

In pgAdmin I have removed a few order entries, but the items
associated to them are still here, now I have zombie items floating
around that don't belong to any order. So how do I tell PgSQL that
when I remove a given order, it should remove all associated items
also?

Best regards,

On Sun, Oct 26, 2008 at 10:44:29AM +0100, Thomas wrote:
> In pgAdmin I have removed a few order entries, but the items
> associated to them are still here, now I have zombie items floating
> around that don't belong to any order. So how do I tell PgSQL that
> when I remove a given order, it should remove all associated items
> also?

use foreign keys.

syntax to add them:
1. "references table (field)" in create table/
2. alter table add foreign key

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Super! Thank you Hubert and Niklas.

On 26 okt 2008, at 10.44, Thomas wrote:
> Currently I have 3 tables:
>
> Product(id, title, price)
> Item(id, product_id, order_id, quantity)
> Order(id, amount, paid)
>
> So how do I tell PgSQL that
> when I remove a given order, it should remove all associated items
> also?

Use a foreign key constraint with the appropriate action:

CREATE TABLE Item (
   id SERIAL PRIMARY KEY,
   product_id INTEGER NOT NULL REFERENCES Product(id) ON DELETE
RESTRICT ON UPDATE CASCADE,
   order_id INTEGER NOT NULL REFERENCES Order(id) ON DELETE CASCADE
ON UPDATE CASCADE,
   quantity NUMERIC(5,2) NOT NULL
);


For more info, see the docs: http://www.postgresql.org/docs/8.3/
interactive/ddl-constraints.html#DDL-CONSTRAINTS-FK



Sincerely,

Niklas Johansson