CODEWITHSUNDEEP
sqlpostgresqlforeign-keys
OJFord
OJFord

Reputation: 11130

Delete row, setting foreign key references to other

I frequently want to do a sort of 'merge' on table a, which is referenced by b like:

UPDATE b SET a=1 WHERE a=2;
DELETE FROM a WHERE id=2;

'merging' a's row id=2 into id=1, updating the foreign key(s) pointing at it, rather than cascading the delete.

Clarification: 1 & 2 is just an example, it might be on a given occasion that (3 & 6), (10 & 27), and (10 & 42) need to be 'merged'. And then another day there might be more.

It's a bit of a pain, especially when there are multiple tables with foreign keys into a. Is there a more convenient way of handling this? I'm imagining something such as:

--PSEUDOCODE NOT VALID SQL
DELETE FROM a WHERE id=2 ON FKVIOLATION SET fk=1;

Upvotes: 0

Views: 54

Answers (1)

BobRodes
BobRodes

Reputation: 6165

There is. You can set a default value for the foreign key, and use ON CASCADE SET DEFAULT in your FK constraint.

Using your example, where b.a is a foreign key referencing a.id, and you want to set orphaned FKs to 1:

ALTER TABLE b ALTER a SET DEFAULT 1;
ALTER TABLE b DROP CONSTRAINT fk_whatever,
ALTER TABLE b ADD FOREIGN KEY (a) REFERENCES id ON CASCADE SET DEFAULT;

This will set orphan foreign keys to whatever default value is set for the foreign key. The only way it wouldn't work is if you were already using a different default value for something else.

Upvotes: 1

Related Questions