Update Parent and Child Table Simultaneously

Question

I need to update a column in two tables that are joined by a primary/foreign key. My problem is that when I try to update either table separately, I get either of the following (dependant upon which table I try and update):

ORA-02292: integrity constraint (URMS.EMSR_EMS_FK) violated - child record found

or

ORA-02291: integrity constraint (URMS.EMSR_EMS_FK) violated - parent key not found

Is there a way to update both tables at exactly the same time with the new value? A solution I've found is to copy the existing rows and insert these as new rows, which can then be updated -- the old rows can then be deleted.

Is this the only solution or is there an easier way around this?

Answer 1

You have a few options as workarounds.

• Change the constraint to a deferrable constraint and defer it. This causes the key to be checked on commit time rather than update time.
• Update the foreign key to NULL first, then update the primary key, then update the foreign key again. This assumes no NOT NULL constraint
• If worse comes to worse, create a record (let's say give it a reserved ID of 0 so you can detect and avoid conflicts), update the foreign key to that, then update the primary key, then update the foreign key, then delete the record.

However there is no way to create a statement that updates both at the same time in Oracle as you might have with ON UPDATE CASCADE in some other databases.