Re: DELETE CASCADE

On Fri, Jun 4, 2021 at 3:40 PM Isaac Morland <isaac(dot)morland(at)gmail(dot)com>
wrote:

> 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.)

David