aspose file tools*
The moose likes Oracle/OAS and the fly likes updating primary key Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "updating primary key" Watch "updating primary key" New topic
Author

updating primary key

Prasanna Kumaar
Ranch Hand

Joined: Feb 08, 2011
Posts: 30
i need to update a column say(employee ID), in a table say (table-A) which is primary key...and it is also foreign key of (table-B) ..when i try to change the primary key(employee Id) in table A it throws error child records found in table -B...is there any way to update a primary key without deleting its child records?
Gene Hilpert
Ranch Hand

Joined: May 22, 2002
Posts: 51

Well one way is to insert a copy of the parrent record with he new primary key then update the child fkey with he new parents key , then delete the old parent.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Prasanna Kumaar wrote:i need to update a column say(employee ID), in a table say (table-A) which is primary key...and it is also foreign key of (table-B) ..when i try to change the primary key(employee Id) in table A it throws error child records found in table -B...is there any way to update a primary key without deleting its child records?


Gene is right. An update of a PK is not really a logical operation to try in a relational database. The PK identifies the row, change the identifier and you are (effectively) changing the thing it identifies. Most, if not all, databases will implement this as requiring an insert and delete, not an update.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
deo swaroop
Ranch Hand

Joined: May 21, 2008
Posts: 30
One work around can be like this ....

Drop the foreign key constraint of (table-B) and after updating again add the foreign key constraint.

Regards,
Deo Swaroop
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

DDL is not transactional. What happens if another thread inserts invalid data while the constraint was disabled? Gene's suggestion is a better bet.
Prasanna Kumaar
Ranch Hand

Joined: Feb 08, 2011
Posts: 30
Gene Hilpert wrote:Well one way is to insert a copy of the parrent record with he new primary key then update the child fkey with he new parents key , then delete the old parent.



Pardon me!!I don't get you,can you explain a bit clearly?
Gene Hilpert
Ranch Hand

Joined: May 22, 2002
Posts: 51

Sorry I have not been watching this forum.

1) first create a copy of the orginal parent record with a new Primary key.
2) change the FK on the child record to the primary key of the new parent record you just added.
3) delete the old parent record because you dont need it any more.
4) ask your DBA why he or she would ever use as a primary key data that might need to be changed. (well ... maybe you should not ask this question)
Prasanna Kumaar
Ranch Hand

Joined: Feb 08, 2011
Posts: 30
Gene Hilpert wrote:Sorry I have not been watching this forum.

1) first create a copy of the orginal parent record with a new Primary key.
2) change the FK on the child record to the primary key of the new parent record you just added.
3) delete the old parent record because you dont need it any more.
4) ask your DBA why he or she would ever use as a primary key data that might need to be changed. (well ... maybe you should not ask this question)



It worked ....Thanks
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: updating primary key