Re: How to tell PostgreSQL about a relationship

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