Re: Trigger violates foreign key constraint

Laurenz Albe <laurenz.albe@cybertec.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