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

Re: How to tell PostgreSQL about a relationship

From: Niklas Johansson <pgmailings(at)codecraft(dot)se>
To: Thomas <iamkenzo(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: How to tell PostgreSQL about a relationship
Date: 2008-10-26 12:57:04
Message-ID: 44FDB7E9-CCFE-4180-8C14-586AA78368AC@codecraft.se (view raw or flat)
Thread:
Lists: pgsql-general
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




In response to

Responses

pgsql-general by date

Next:From: ThomasDate: 2008-10-26 13:12:36
Subject: Re: How to tell PostgreSQL about a relationship
Previous:From: hubert depesz lubaczewskiDate: 2008-10-26 12:44:41
Subject: Re: How to tell PostgreSQL about a relationship

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