Trigger violates foreign key constraint

Laurenz Albe <laurenz.albe@cybertec.at>

From: Laurenz Albe <laurenz.albe@cybertec.at>
To: pgsql-hackers@postgresql.org
Date: 2023-10-02T07:03:33Z
Lists: pgsql-hackers
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 NULL; END;';

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

INSERT INTO parent VALUES (1);

INSERT INTO child VALUES (1);

DELETE FROM parent WHERE id = 1;

TABLE child;
 id 
════
  1
(1 row)

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

Yours,
Laurenz Albe



Commits

  1. Doc: document that triggers can break referential integrity.