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;
What you are asking is impossible: the child row would have to secretly remember its previous foreign key value and automatically restore it whenever a row is inserted in the parent table.
I think that the solution in this case is to have no foreign key at all, but to display the "foreign key" column as NULL if there is no matching parent row:
SELECT c.child_id, p.parent_id
FROM child AS c LEFT JOIN parent AS p USING (parent_id);
You could define a view over this query and use that as the child "table", then it would exhibit the properties you want.