Is it possible to prevent delete but not an insert with foreign key?

Question

I have two tables that I would like to link with a single-column foreign key. A normal parent-child type thing. The problem I'm trying to solve is that the nature of the system is such that the child can arrive before the parent exists (the child knows the what the id of the parent will be).

I don't want to delay the insert of the child waiting for the parent to arrive, speed is important. But I do want some referential integrity - I don't want to be able to delete the parent if the child exists.

I'm assuming I can't achieve this with a regular foreign key in Postgresql? Is the solution to write some sort of trigger?

Perhaps you should always insert into the parent if the child arrives first, and then when the parent “arrives,” do an upsert — richyen, Nov 22 '19 at 09:08

score 3 · Answer 1 · answered Nov 22 '19 at 09:06

3

You could do this with a "deferrable" constraint.

create table child (parent_id integer references parent deferrable initially immediate);

You can then use set constraints in a transaction to temporarily defer the constraint like this:

start transaction;
set constraints all deferred;
-- Your inserts go here --
commit;

answered Nov 22 '19 at 09:06

Ragesh

2,800
2
25
36

The two inserts are not going in one transaction, they could be several seconds apart. Will this solution still work? – pomo Nov 23 '19 at 11:08
1

Constraints can only be deferred within a transaction. This solution will not work if you're not doing all the inserts in a single transaction. – Ragesh Nov 23 '19 at 13:28

Nick Barnes · Accepted Answer · 2019-11-22T15:23:55.273

If you are creating the parent and child within the same database transaction and you cannot control the insert order, then the right approach is a deferred constraint, as suggested by @Ragesh.

Otherwise, you guessed right: the solution is to write some sort of trigger.

Foreign key constraints in Postgres are little more than a pair of triggers, specifically:

A trigger on INSERT (or UPDATE of the FK) of the child, to check that the parent exists, and to acquire a KEY SHARE lock to ensure that the parent doesn't disappear before the COMMIT
A trigger on DELETE (or UPDATE of the PK) of the parent to check that it has no children

In this case, you just want the parent's trigger, but not the child's. Luckily, it's pretty straightforward to write yourself. Something like this should do it:

create table parent (id int primary key);
create table child (id int primary key, parent_id int);

create function trg_parent_fk_check() returns trigger language plpgsql as $$
begin
  if exists(select 1 from child where parent_id = old.id) then
    raise foreign_key_violation using
      message = 'parent.id=' || old.id || ' is still referenced by child.parent_id';
  end if;
end
$$;

create trigger trg_parent_fk_check_del
after delete on parent
for each row
execute function trg_parent_fk_check();

/* You probably shouldn't be modifying primary keys, but just in case... */
create trigger trg_parent_fk_check_upd
after update of id on parent
for each row
when (old.id <> new.id)
execute function trg_parent_fk_check();

Of course, this approach allows for the possibility that the child arrives, but the parent doesn't. You may want to include some scheduled process to find and delete orphaned records.

The two inserts are not in the same transaction – pomo Nov 23 '19 at 11:09 — pomo, Nov 23 '19 at 11:09

Is it possible to prevent delete but not an insert with foreign key?

2 Answers2