none
Override a foreign key constraint

    Question

  • A website that I'm working on has users sign in and keeps a log of the pages they go to.  The log table has a foreign key in it that links to the username is the users table.  I need to update the username for one of the users but the foreign key is preventing me from doing so.  What is the benefit of having a foreign key like this?  Can I delete it to update the username or is there a better way?
    Thursday, August 09, 2007 1:19 PM

Answers

  •  

    Foreign Key constraints ensure the integrity of the data is maintained. If there was no FK in place. then it would be easy to get orphaned and inconsistent data. In your example, if you just update a username to be Bob from Terry then all the records associated with Terry in the log table will now have no link back to the users record ie the Terry records will be orphaned.

     

    Soooooo, in answer to your question, you can drop it make your update but it will throw an error when you try and recreate the FK unless you update the log records too.

     

    You can create these constraints with a cascading updates/deletes which will filter your changes down to the child tables but in this case, it looks like its turned off.

     

    I'd suggest just doing the following

     

    Psuedo code:

     

    UPDATE Log set Username = Bob WHERE username  = Terry

    UPDATE user set username = Bob WHERE username = Terry.

     

    HTH!

    Thursday, August 09, 2007 2:00 PM
    Moderator