Hello,
we have a database with around 30 tables which have a lot foreign key constraints. For some reason we have to change all the primary keys (and therefore the foreign keys obviously as well). Lets say we have to add 1000 to every key.
We wrote a script like this
Code:
UPDATE TABLE1 SET ID = ID + 1000, FK1 = FK1 + 1000, FK2 = FK2 + 1000
UPDATE TABLE2 SET ID = ID + 1000, FK1 = FK1 + 1000, FK2 = FK2 + 1000, FK3 = FK3 + 1000
UPDATE TABLE3 SET ID = ID + 1000, FK1 = FK1 + 1000, FK2 = FK2 + 1000
Unfortunately we are getting an SQL error with SQLSTATE 23504 "PARENT KEY IN A PARENT ROW CANNOT BE UPDATED BECAUSE IT HAS ONE OR MORE DEPENDENT ROWS IN RELATIONSHIP". I thought it should be possible to temporarly violate the foreign key constraints inside a transcation as long as they are ok at the end of the transaction, but apperently I'm wrong.
What do we have to do for being able to exceute the script.
Thanks in advance,
Ole
dBforums
>
Updates on Keys within Transaction
03-11-10, 03:57
Linear Mode
