Consider a basic relational pattern (one parent, multiple children)
As part of a transaction, I may delete parents, but I may also re-insert them. In the first case, I want the foreign key to become NULL. In the second case, I would like to preserve the relational integrity. Is there a way to declare the foreign key so that it satisfies both requirements?
create table parent
(
parent_id integer primary key
);
create table child
(
child_id integer primary key,
parent_id integer references parent (parent_id)
on delete set null
);
insert into parent (parent_id) VALUES (1), (2), (3);
insert into child (child_id, parent_id) VALUES (1, 1), (2, 2), (3, 3);
BEGIN;
delete from parent where parent_id = 1;
COMMIT;
-- expecting NULL
select parent_id from child where child_id = 1;
BEGIN;
delete from parent where parent_id = 2;
insert into parent (parent_id) VALUES (2);
COMMIT;
-- would like 2, but getting NULL
select parent_id from child where child_id = 2;
DEFERRABLEdoes not extend to theon delete set nulljust the child table check that the parent key exists. 2) In 1) I realized you usedon delete set nullwhich is going to set the whole row inchildNULL. You probably wanton delete set null parent_id. At least then you will not lose all the information. 3) Have you looked at ON CONFLICT?ON DELETE SET NULLwithout a column list does not set the entire row toNULLvalues but only the columns that are subject to the foreign key constraint