Message-ID: <b81fe38fcc25a81be6e2e5b3fc1ff624130762fa.camel@cybertec.at>
Subject: Trigger violates foreign key constraint
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: pgsql-hackers@postgresql.org
Date: Mon, 02 Oct 2023 09:03:33 +0200
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.48.4 (3.48.4-1.fc38) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/b81fe38fcc25a81be6e2e5b3fc1ff624130762fa.camel%40cybertec.at>
Precedence: bulk

CREATE TABLE parent (id integer PRIMARY KEY);

CREATE TABLE child (id integer REFERENCES parent ON DELETE CASCADE);

CREATE FUNCTION silly() RETURNS trigger LANGUAGE plpgsql AS 'BEGIN RETURN N=
ULL; END;';

CREATE TRIGGER silly BEFORE DELETE ON child FOR EACH ROW EXECUTE FUNCTION s=
illy();

INSERT INTO parent VALUES (1);

INSERT INTO child VALUES (1);

DELETE FROM parent WHERE id =3D 1;

TABLE child;
 id=20
=E2=95=90=E2=95=90=E2=95=90=E2=95=90
  1
(1 row)

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

Yours,
Laurenz Albe