From: | Jan Wieck <JanWieck(at)Yahoo(dot)com> |
---|---|
To: | Brian Sanders <bsanders(at)hotchkiss(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Opposite value for RESTRICT in foreign keys? |
Date: | 2003-05-19 20:19:43 |
Message-ID: | 3EC93C5F.7010100@Yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Brian Sanders wrote:
> I apologize if this is a simple question, but after much searching
> through google and the list archives, I cannot find any answer. My
> question is this:
>
> Say I have two tables:
>
> CREATE TABLE products(
> id int,
> name varchar(50)
> );
>
> CREATE TABLE orders(
> id int,
> product_id int,
> customer varchar(100)
> FOREIGN KEY (product_id) REFERENCES products(id) ON UPDATE CASCADE ON
> DELETE ???
> );
>
>
> Is there any way to create a foreign key that *allows* you to delete the
> matching row from the referenced table (i.e. does the opposite of "NO
> ACTION" or "RESTRICT")?
>
> Basically, what I want to happen is:
>
> * Whenever an order is created, the product_id field in the new record
> MUST match an existing id field in the products table. (I don't want to
> take orders for products that don't exist.)
> * Whenver a product id is changed, all corresponding product_id's in the
> orders table MUST be updated. (I want orders to match their respective
> products even when the product's id is changed.)
> * Whenever a product is deleted, all corresponding product_id's MUST NOT
> be changed or deleted. (Even though the product is gone, I still want
> the product_id to stay the same.)
>
> As you can see I need referential integrity for every operation EXCEPT
> deletion.
>
> Is there any solution for this without scrapping the foreign key
> altogether?
Not with referential integrity as per specification. It has to guarantee
the integrity at all times, not just at reference creation. You have to
do this with custom triggers.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #
From | Date | Subject | |
---|---|---|---|
Next Message | Sarah | 2003-05-19 20:59:00 | plpgsql error? |
Previous Message | scott.marlowe | 2003-05-19 20:13:58 | Re: PostgreSQL Performance on OpenBSD |