#1
  1. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Sep 2006
    Posts
    2,056
    Rep Power
    535

    Updating columns with FK constraints


    I have a table which contains a foreign key referencing another table.

    By design, a record in the table stores a value in the FK column which is not represented in the other table.

    I found that I could update other columns in that record without turning FK constraints off.

    This kind of surprised me.

    Is this to be expected future behavior, or should I disable FK constraints whenever updating such a record?
  2. #2
  3. Code Monkey V. 0.9
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Mar 2005
    Location
    A Land Down Under
    Posts
    2,210
    Rep Power
    1992
    There's no reason to disable FK constraints, especially when you're updating. The constraints are what keeps the data correct.

    If I'm getting what you're saying right, you're updating columns in a table that has a foreign key, but you're not updating the column that holds the FK? If that's the case, that's exactly what it should be doing. There should be no restriction on updating a column that's not part of a FK restraint. If there was it would make things a terrible mess. It's only the column that contains the FK that you need to be concerned about, but you can still update that as long as it has a valid value in the other table.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,397
    Rep Power
    4286
    Originally Posted by NotionCommotion
    By design, a record in the table stores a value in the FK column which is not represented in the other table.
    then it can't be an actual FOREIGN KEY

    if you think it is, could you please prepare a dump of some test data that demonstrates your situation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo