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