Options
Sync Fails due to Primary Key Constraint
DBNewbie_2007
Posts: 47 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).
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
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.