SQL Server Performance

Need to update Primary Key Column data which is in Trans Replication

Discussion in 'SQL Server 2005 Replication' started by mak78, Jan 12, 2010.

  1. mak78 New Member

    Hi,
    Tablewhich is in transaction push replication to SS2K8 from SS2K5 needs anemployee name to be changed which has PK and FK with other Parent and ChildTables.

    Table_to_be_Updated(PK_Col1,Col2,Col3)
    Table_being_referrenced(PK_col,col fk PK_Col1,cola,colb)

    Error while updating the table with PK n itself as FK in other table:
    Msg 547, Level 16, State 0, Line 1
    The UPDATE statement conflicted with the REFERENCE constraint "FK************__59C55456".

    The conflict occurred in database "abcd", table "dbo.Abc", column 'col_uid'.
    The statement has been terminated.

    I am afraid any changes to the constraints for update will break replication.
    Updation is not allowed until the ON UPDATE CASCADEoption is used, which may cause huge complexities updating all relatedtables.
    Any suggestions .......

    Thanks in Advance
  2. preethi Member

    This is where the best solution is to keep a key which does not get changed as the primary key.
    Now for your scenario, the best option is to disable the FK and perform the update. You also need to update all referenced tables separately. CASCADE option will do the same thing in one go, but will lead to many tables being locked until the entire operation is completed.
    If you have the time and if you application can support, you need to re think of the database design you have,

Share This Page