I apologize if I am deeply confused, but say I have this:
CREATE TABLE parent (
pid int primary key,
parent_data text
);
CREATE TABLE child (
pid int REFERENCES parent,
cid int,
PRIMARY KEY (pid, cid),
child_data text
);
It's easy to imagine needing to write:
DELETE FROM child WHERE ...
DELETE FROM parent WHERE ...
... where the WHERE clauses both work out to the same pid values. It would be nice to be able to say:
DELETE CASCADE FROM parent WHERE ...
... and just skip writing the first DELETE entirely. And what do I mean by "DELETE CASCADE" if not "delete the referencing rows from child"? So to me I think I should require DELETE permission on child (and parent) in order to execute this DELETE CASCADE. I definitely shouldn't require any DDL-related permissions (table owner, REFERENCES, …) because I'm not doing DDL - just data changes. Sure, it may be implemented by temporarily treating the foreign key references differently, but conceptually I'm just deleting from multiple tables in one command.
I will say I would prefer this syntax:
DELETE FROM parent WHERE ... CASCADE TO child;
(or "CASCADE TO ALL TABLES" or some such if I want that)
I don't like the idea of saying "CASCADE" and getting a bunch of tables I didn't intend (or which didn't exist when the query was written).
