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

sql oracle hierarchical-data referential-integrity
aw crud picture aw crud · Jun 23, 2010 · Viewed 9.1k times · Source

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

Chi picture Chi · Jun 23, 2010

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.

Related questions

SQL recursive query on self referencing table (Oracle)

Lets assume I have this sample data: | Name | ID | PARENT_ID | ----------------------------- | a1 | 1 | null | | b2 | 2 | null | | c3 | 3 | null | | a1.d4 | 4 | 1 | | a1.e5 | 5 | 1 | | a1.d4.f6 | 6 | 4 | | a1.d4.g7 | 7 | 4 | | a1.e5.h8 | 8 | 5 | | a2.i9 | 9 | 2 | | a2.i9.j10| 10 | 9 | I would like to …

Read More
Tree structure in sql in Oracle.How to show tree,child nodes and parent nodes in SQL Oracle

I would like to show a tree structure in SQL with child nodes and parent nodes. I have a table like: Employee ------------- ID (int) FirstName (varchar) LastName (varchar) ParentID (int) Job (varchar) which represents an employee. ParentID represent the …

Read More
Oracle SQL Hierarchical Query: Flatten Hierarchy and Perform Aggregation

I am trying to improve performance for a proof of concept I have already written and am having no luck. I think the approach is probably flawed, but I’m struggling to find another solution. I’ve covered all the …

Read More