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.

Answer 1

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.