Re: Trigger violates foreign key constraint

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Trigger violates foreign key constraint
Date: 2023-10-02 13:49:53
Message-ID: 2014740.1696254593@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> writes:
> CREATE FUNCTION silly() RETURNS trigger LANGUAGE plpgsql AS 'BEGIN RETURN NULL; END;';
> CREATE TRIGGER silly BEFORE DELETE ON child FOR EACH ROW EXECUTE FUNCTION silly();

> The trigger function cancels the cascaded delete on "child", and we are left with
> a row in "child" that references no row in "parent".

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

There are good reasons to want triggers to be able to see and
react to FK-driven updates, so it's unlikely that we'd want to
revisit that design decision, even if it hadn't already stood
for decades.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Laurenz Albe 2023-10-02 14:11:59 Re: Trigger violates foreign key constraint
Previous Message Tom Lane 2023-10-02 13:39:14 Re: Making the subquery alias optional in the FROM clause