Intuitively, adding the option ON UPDATE CASCADE to a foreign key constraint will have the effect of updating all referencing columns with the updated value of the key. Would that really work?
Example lifted from manual:
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
CREATE TABLE orders (
order_id integer PRIMARY KEY,
shipping_address text,
...
);
CREATE TABLE order_items (
product_no integer REFERENCES products ON DELETE RESTRICT,
## HERE'S THE LINE THAT I MODIFIED FROM THE MANUAL, IT USED TO SAY 'ON DELETE CASCADE'
order_id integer REFERENCES orders ON UPDATE CASCADE,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
I am using Postgres 9.1.
ON DELETE CASCADE
. You can have a foreign key with both behaviours:order_id integer REFERENCES orders ON DELETE CASCADE ON UPDATE CASCADE
– ypercubeᵀᴹ Sep 20 '12 at 19:16