Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    4

    Unanswered: UPDATE problem with foreign key constraints

    I have a table that I want to run an UPDATE statement to change some data in the table, but I get this error:

    Server: Msg 547, Level 16, State 1, Line 1
    UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'FK__Yield__Financial_Product__ProductCode'. The conflict occurred in database 'lsmdb', table 'Financial_Product', column 'ProductCode'.
    The statement has been terminated.


    How do I update the ProductCode column in both tables to reflect my updated data?

    Thanks in advance.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You need to either delete all of the children rows and add them with the new key, or add ON UPDATE CASACDE to your RI...


    Look here

    http://www.dbforums.com/t982357.html
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Feb 2004
    Posts
    4
    Originally posted by Brett Kaiser
    You need to either delete all of the children rows and add them with the new key, or add ON UPDATE CASACDE to your RI...


    Look here

    http://www.dbforums.com/t982357.html


    Thanks, Brett, but I'm a newbie, so I can't figure out how to apply the code you referred me to to my situation. I have 2 tables(Financial_Product & Yield) which already have tons of data stored in them. What I need to do is change the data in all instances of the ProductCode column where it says 'x' to 'y'. What I'm getting out of the code you referred me to is dropping then re-creating my tables, but what about the data already in them that I want left untouched?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Mine is just an example that you could cut and paste in to Query Analyzer...

    Maybe if you can explain the problem a little more...

    Is this a one time (biggest lie in IT btw) deal?

    If so, you need to add (ie INSERT) the new Parent, then UPDATE all the children and DELETE the Old parent...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Feb 2004
    Posts
    4
    Originally posted by Brett Kaiser
    Mine is just an example that you could cut and paste in to Query Analyzer...

    Maybe if you can explain the problem a little more...

    Is this a one time (biggest lie in IT btw) deal?

    If so, you need to add (ie INSERT) the new Parent, then UPDATE all the children and DELETE the Old parent...

    Keeping in mind the biggest lie, yes it is a one time deal, where the person who input the data entered in the wrong value, but the application where the data was entered has no edit or delete functionality.

    So what you're saying is to re-create the new parent with the correct data, then delete the old, update the child, then re-establish the foreign key?

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well, if I where to do this....

    1. DUMP THE DATABASE ...plan for a F_CK UP

    2. INSERT the new PARENT

    3. UPDATE the Children

    4. Audit the data with SELECT statements to make sure everything is OK

    5. DELETE the old Parent

    6. Check the data again (dba's are pessimistic by nature, or should be)

    7. DUMP the databas eagain (See the latter part of item 6)

    Good Luck
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Feb 2004
    Posts
    4

    Thumbs up

    Thanks, Brett for your patience, that did the trick!

Posting Permissions

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