updating primary key

[Prasanna Kumaar]

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]

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]

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.

[deo swaroop]

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]

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]

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]

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]

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