Re: Trigger violates foreign key constraint

On Tue, Oct 3, 2023 at 12:52 AM Laurenz Albe <laurenz.albe@cybertec.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.