Is it possible to modify the value of a record's primary key in Oracle when child records exist?

Question

I have some Oracle tables that represent a parent-child relationship. They look something like this:

create table Parent (
    parent_id varchar2(20) not null primary key
);

create table Child (
    child_id number not null primary key,
    parent_id varchar2(20) not null,

    constraint fk_parent_id
        foreign key (parent_id)
        references Parent (parent_id)
);

This is a live database and its schema was designed long ago under the assumption that the parent_id field would be static and unchanging for a given record. Now the rules have changed and we really would like to change the value of parent_id for some records.

For example, I have these records:

Parent:

parent_id
---------
ABC123


Child:

child_id  parent_id
--------  ---------
1         ABC123
2         ABC123

And I want to modify ABC123 in these records in both tables to something else.

It's my understanding that one cannot write an Oracle update statement that will update both parent and child tables simultaneously, and given the FK constraint, I'm not sure how best to update my database. I am currently disabling the fk_parent_id constraint, updating each table independently, and then enabling the constraint.

Is there a better, single-step way to update this content?

Answer 1

There are no cascading updates.

You could use a deferrable constraint.

Or, within a transaction:

• Copy the Parent to a new row with the new key:

  INSERT INTO Parent (key, cols...) SELECT newkey, cols... FROM Parent WHERE key = oldkey

• Assign all the children:

  UPDATE Child SET parent_id = newkey WHERE parent_id = oldkey

• Delete the parent now that no one is referencing it:

  DELETE FROM Parent WHERE key = oldkey

See this link.

Answer 2

Hi Chris,

If you find you need to update your primary key frequently, you may need to rethink your model and use a true immutable primary key. Use a column with a surrogate key that has no meaning (an incrementing identity column or a GUID) and store the value you want to update only in the parent table (so that when you need to update it you only have one row to modify).

Answer 3

Tom Kyte has provided a utility to implement "update cascade" functionality here.