How can I break referential integrity briefly, within a transaction, without disabling the foreign key constraint?

Question

I have a table with 3 columns:

ID, PARENT_ID, NAME

PARENT_ID has a foreign key relationship with ID in the same table. This table is modeling a hierarchy.

Sometimes the ID of a record will change. I want to be able to update a record's ID, then update the dependent records' PARENT_ID to point to the new ID.

The problem is, when I attempt to update the ID of a record it breaks the integrity and fails immediately.

I realize I could insert a new record with the new ID, then update the children, then delete the old record, but we have a lot of triggers in place that would get screwed up if I did that.

Is there any way to temporarily update the parent with the promise of updating the children (obviously it would fail on commit) without disabling the foreign key briefly?

Answer 1

What you want is a 'deferred constraint'.

You can pick between the two types of deferrable constraints, 'INITIALLY IMMEDIATE' and 'INITIALLY DEFERRED' to drive default behavior - whether the database should default to check the constraint after every statement, or if it should default to only checking constraints at the end of the transaction.