SQL Server Performance

Update foreign key

Discussion in 'General Developer Questions' started by vietcave, Jul 26, 2005.

  1. vietcave New Member

    Hi everyone,

    I have 2 tables.

    CREATE TABLE t1(
    id int PRIMARY KEY,
    name char(20)
    )


    CREATE TABLE t2(
    id int REFERENCES t1(id),
    class char(20)
    )

    How can I do an operation that updates the column "id" of the table t1 without deleting the foreign key on the table t2?

    Thanks.
  2. Adriaan New Member

    To see what happens, add this to your script:<br /><br />insert into t1 values (1, 'a')<br />insert into t2 values (1, 'f')<br />update t1 set id = 2<br /><br />You will see an error occurring for the last line:<br />Server: Msg 547, Level 16, State 1, Line 1<br />UPDATE statement conflicted with COLUMN REFERENCE constraint '&lt;FK_name&gt;'. The conflict occurred in database '&lt;db_name&gt;', table 't2', column 'id'.<br />The statement has been terminated.<br /><br />Your relationship does not specify a cascade action to be taken, so this effectively blocks changes to t1.id if there are corresponding records in t2.<br /><br />Given your non-cascading relationship, if you want to change the id reference in t2, you must first check if the new id value is present in t1 (if not then insert that into t1) and then you can update the old id value in t2 with the new id value.<br /><br />This would be a pretty non-standard solution, so I have to ask if you understand the concept of cascading relationships? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]

Share This Page