Hi -hackers,
Presented for discussion is a POC for a DELETE CASCADE functionality, which will allow you one-shot usage of treating existing NO ACTION and RESTRICT FK constraints as if they were originally defined as CASCADE constraints. I can't tell you how many times this functionality would have been useful in the field, and despite the expected answer of "define your constraints right in the first place", this is not always an option, nor is the ability to change that easily (or create new constraints that need to revalidate against big tables) always the best option.
That said, I'm happy to quibble about the specific approach to be taken; I've written this based on the most straightforward way I could come up with to accomplish this, but if there are better directions to take to get the equivalent functionality I'm happy to discuss.
From the commit message:
Proof of concept of allowing a DELETE statement to override formal FK's handling from RESTRICT/NO
ACTION and treat as CASCADE instead.
Syntax is "DELETE CASCADE ..." instead of "DELETE ... CASCADE" due to unresolvable bison conflicts.
Sample session:
postgres=# create table foo (id serial primary key, val text);
CREATE TABLE
postgres=# create table bar (id serial primary key, foo_id int references foo(id), val text);
CREATE TABLE
postgres=# insert into foo (val) values ('a'),('b'),('c');
INSERT 0 3
postgres=# insert into bar (foo_id, val) values (1,'d'),(1,'e'),(2,'f'),(2,'g');
INSERT 0 4
postgres=# select * from foo;
id | val
----+-----
1 | a
2 | b
3 | c
(3 rows)
postgres=# select * from bar;
id | foo_id | val
----+--------+-----
1 | 1 | d
2 | 1 | e
3 | 2 | f
4 | 2 | g
(4 rows)
postgres=# delete from foo where id = 1;
ERROR: update or delete on table "foo" violates foreign key constraint "bar_foo_id_fkey" on table "bar"
DETAIL: Key (id)=(1) is still referenced from table "bar".
postgres=# delete cascade from foo where id = 1;
DELETE 1
postgres=# select * from foo;
id | val
----+-----
2 | b
3 | c
(2 rows)
postgres=# select * from bar;
id | foo_id | val
----+--------+-----
3 | 2 | f
4 | 2 | g
(2 rows)
Best,
David
