Re: Trigger violates foreign key constraint

On Tue, Oct 3, 2023 at 12:52 AM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Mon, 2023-10-02 at 09:49 -0400, Tom Lane wrote:
> > This is by design: triggers operate at a lower level than
> > foreign keys, so an ill-conceived trigger can break an FK constraint.
> > That's documented somewhere, though maybe not visibly enough.
>
> Not having found any documentation, I propose the attached caution.
>
>
I dislike scaring the user like this without providing any context on what
conditions or actions are problematic.

The ON DELETE and ON UPDATE clauses of foreign keys are implemented as
system triggers on the referenced table that invoke additional delete or
update commands on the referencing table. The final outcome of these
additional commands are not checked - it is the responsibility of the DBA
to ensure that the user triggers on the referencing table actually remove
the rows they are requested to remove, or update to NULL any referencing
foreign key columns. In particular, before row triggers that return NULL
will prevent the delete/update from occurring and thus result in a violated
foreign key constraint.

Add sgml as needed, note the original patch missed adding "<productname>"
to PostgreSQL.

David J.