From: | "Brian Sanders" <bsanders(at)hotchkiss(dot)org> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Opposite value for RESTRICT in foreign keys? |
Date: | 2003-05-09 18:46:28 |
Message-ID: | sebbbf5b.051@grpwise.hotchkiss.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
Thanks,
Brian Sanders
Webmaster
The Hotchkiss School
bsanders(at)hotchkiss(dot)org
860-435-3141
From | Date | Subject | |
---|---|---|---|
Next Message | Adam Kessel | 2003-05-09 20:48:49 | Caching Websites |
Previous Message | Keary Suska | 2003-05-09 18:36:23 | semget: No space left on device |