How can we help you today? How can we help you today?

Sync Fails due to Primary Key Constraint

I am trying to synchronize two tables in two different databases. The table structures are indentical (primary keys have different names but they use same key fields).

I tried to sync the databases but receive the following error message:
[2627] Violation of UNIQUE KEY constraint 'PK_TkNo'. Cannot insert duplicate key in object 'dbo.MyTable'.

I found the duplicate, deleted it in the destination table, then tried to re-run the sync (long story on why this could not be an update!). However, it fails again with the same error and the same insert statement! I try to do a REFRESH COMPARISON, but again, get a failure on the Primary key for the same record when trying to sync...

I can save the SQL Statement from RedGate, find the insert statement on the "bad" record, copy it and manually run the insert command, which runs correctly. I again do a REFRESH COMPARISON and now it works.

Any suggestions or comments?

RedGate Data Compare version 6.1.0.206 (SQL Server 2005 SP2).
DBNewbie_2007
0

Comments

1 comment

Sort by
  • Brian Donahue
    Hi John,

    SQL Data Compare can get you into this kind of situation becuase it does not organize updates in a way that will prevent the violation of constraints during synchronization; it updates records in batches by table.

    Thankfully you can use the 'disable primary keys and indexes' and 'disable foreign keys' settings to work around this.
    Brian Donahue
    0

Add comment

Please sign in to leave a comment.