0

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;
5
  • 1
    1) There is not such a solution with FK's. 2) What is the purpose of this? Commented Jan 11, 2024 at 17:54
  • 1) OK I figured maybe playing with defers would help. 2) It's a bit of a theoretical question, but I also wanted to convert updates into delete+insert to make a dataset compatible with timescaledb hypertables. However it's impacting foreign keys of other tables. Commented Jan 11, 2024 at 17:58
  • 1) I don't have time to set up a test, but I pretty sure DEFERRABLE does not extend to the on delete set null just the child table check that the parent key exists. 2) In 1) I realized you used on delete set null which is going to set the whole row in child NULL. You probably want on delete set null parent_id. At least then you will not lose all the information. 3) Have you looked at ON CONFLICT? Commented Jan 11, 2024 at 18:15
  • 1
    @AdrianKlaver ON DELETE SET NULL without a column list does not set the entire row to NULL values but only the columns that are subject to the foreign key constraint Commented Jan 11, 2024 at 20:00
  • @Bergi Arrgh. My bad, writing before coffee. Commented Jan 11, 2024 at 20:17

1 Answer 1

1

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.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.