Sync Fails due to Primary Key Constraint

JReitterJReitter Posts: 42 Bronze 3
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).

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    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.
Sign In or Register to comment.