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 ...
This is entirely the use case and the motivation.
... 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.
This is the part where I'm also running into some conceptual roadblocks between what is an implementation issue based on the current behavior of CASCADE triggers, and what makes sense in terms of a POLA perspective. In part, I am having this discussion to flesh out this part of the problem.
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).
A soft -1 from me here, though I understand the rationale here; you would be unable to manually delete these records with the existing constraints if there were a `grandchild` table without first removing those records too. (Maybe some method of previewing which relations/FKs would be involved here would be a suitable compromise, but I have no idea what that would look like or how it would work.) (Maybe just NOTICE: DELETE CASCADES to ... for each table, and people should know to wrap in a transaction if they don't know what will happen.)
