CASCADE DELETE on two foreign key constraints

Question

I have the following example:

Table A
   -some_id

Table B
   -another_id

Table C
   -some_id_fk
   -another_id_fk

I want to cascade a row on Table C if both some_id and another_id are deleted from their respective tables.

How can I make a row in Table C cascade itself when two foreign keys are deleted?

If only one of the FKs is deleted, the affected row should change to a null value in the column referencing that foreign key.

What should happen when just one of the referenced rows are deleted? Set the FK to `NULL`? — Markus Jarderot, Dec 19 '14 at 00:35
Write two triggers ON DELETE for table A and B to remove a row from the table C if necessary. Or better write two procedures to delete the rows from the table A and B and remove a row from the table C if necessary. — Hovo, Dec 19 '14 at 00:36
@Hovo I knew triggers were an option but was hoping for a better solution — 12preschph, Dec 19 '14 at 00:47
There are no better options. @Hovo has it right. A cascade either sets null or deletes; not conditionally one or the other. After triggers are your only option here. You also need to manage the case where table C is updated so its foreign keys are null, if applicable. — Denis de Bernardy, Dec 19 '14 at 01:24
Are there other rows in `C`? Beyond any audit fields, I mean. If no, consider deleting the record there anyways, as the relationship would be removed. If there are, there may be normalization issues in the design... — Clockwork-Muse, Dec 19 '14 at 06:39

Erwin Brandstetter · Accepted Answer · 2021-03-11T01:04:13.367

I suggest two foreign key constraints with ON DELETE SET NULL and a trigger that takes care of the rest

Tables:

CREATE TABLE a (a_id serial PRIMARY KEY, a text NOT NULL);
CREATE TABLE b (b_id serial PRIMARY KEY, b text NOT NULL);

CREATE TABLE ab (
  ab_id serial PRIMARY KEY
, a_id int REFERENCES a ON DELETE SET NULL
, b_id int REFERENCES b ON DELETE SET NULL
, UNIQUE (a_id, b_id)
);

Trigger:

CREATE OR REPLACE FUNCTION trg_ab_upbef_nulldel()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   DELETE FROM ab WHERE ab_id = NEW.ab_id;
   RETURN NULL;  -- to cancel UPDATE
END
$func$;

CREATE TRIGGER upbef_null2del
BEFORE UPDATE OF a_id, b_id ON ab
FOR EACH ROW
WHEN (NEW.a_id IS NULL AND NEW.b_id IS NULL)
EXECUTE PROCEDURE trg_ab_upbef_nulldel();

db<>fiddle here
_{Old sqlfiddle}

Be sure to have a surrogate PK column for the connecting table. (a_id, b_id) cannot be the PK anyway, because that would disallow NULL in both. Add a UNIQUE constraint instead, which allows NULL values.

The trigger is optimized for performance and only kicks in when one of the two FK columns is updated, and only when that results in both being NULL.

The trigger function deletes the row and returns NULL to cancel the now void cascaded UPDATE.

I get the trigger. The trigger will execute when before both `a_id` and `b_id` is null (within the same row). then the trigger will trigger the function `trg_ab_upbef_nulldel()` `DELETE FROM tblab WHERE ab_id = NEW.ab_id; RETURN NULL;` this part I am not getting it. I thought it will actually delete one row in `ab`. Now actually it will prevent from delete one row from `ab` @Erwin — jian, Dec 02 '21 at 12:38
@JianHe: No delettion from table `ab` is prevented here. Play with the supplied fiddle to see. — Erwin Brandstetter, Dec 02 '21 at 13:01

CASCADE DELETE on two foreign key constraints

1 Answers1

Linked