Trigger violates foreign key constraint

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Trigger violates foreign key constraint
Date: 2023-10-02 07:03:33
Message-ID: b81fe38fcc25a81be6e2e5b3fc1ff624130762fa.camel@cybertec.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2023-10-02 07:12:29 Clean up some pg_dump tests
Previous Message Drouvot, Bertrand 2023-10-02 06:09:01 Re: Synchronizing slots from primary to standby