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 in Table C if both some_id and another_id removed from the respective tables.

How can I make a row in a cascade of table C 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 that references this 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 AS $func$ BEGIN DELETE FROM ab WHERE ab_id = NEW.ab_id; RETURN NULL; END $func$ LANGUAGE plpgsql; CREATE TRIGGER upbef_nulldel 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(); 

SQL Fiddle

• You must have a PK surrogate column for the join table. (a_id, b_id) cannot be PK anyway, because it will disable NULL in both. Instead, add a UNIQUE constraint , which allows you to use NULL values.

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

• Trigger trigger function: Deletes a string and returns NULL to cancel the current cascading UPDATE .