How to tell PostgreSQL about a relationship

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

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

Super! Thank you Hubert and Niklas.