Re: DELETE CASCADE

On Thu, Jun 3, 2021 at 1:49 PM David Christensen <
david(dot)christensen(at)crunchydata(dot)com> wrote:

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

ON DELETE NO ACTION constraints become ON DELETE CASCADE constraints - ON
DELETE SET NULL constraints are ignored, and not possible to emulate via
this feature.

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

Once...but I agreed.

>
> 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.
>
>
This behavior should require the same permissions as actually creating an
ON DELETE CASCADE FK on the cascaded-to tables. i.e., Table Owner role
membership (the requirement for FK permissions can be assumed by the
presence of the existing FK constraint and being the table's owner).

Having the defined FK behaviors be more readily changeable, while not
mitigating this need, is IMO a more important feature to implement. If
there is a reason that cannot be implemented (besides no one has bothered
to take the time) then I would consider that reason to also apply to
prevent implementing this work-around.

David J.