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

Posted by Chris Farmer on Stack Overflow See other posts from Stack Overflow or by Chris Farmer
Published on 2010-05-20T20:27:38Z Indexed on 2010/05/20 20:50 UTC
Read the original article Hit count: 168

Filed under:
|

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?

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about sql