Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2009
    Posts
    26

    Unanswered: Updates on Keys within Transaction

    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

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    alter table ... alter foreign key ... not enforced
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Oct 2009
    Posts
    26
    Thanks a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •