SQL Server 2008 Sync - How to reinsert conflicting pk rows with new primary key

Question

Can someone point me in the right direction for handling a PK conflict error in c# .net using Sync Framework.

I am trying to sync between sql server 2008 and local data cache database (.sdf) and would like to keep BOTH rows that are conflicting in both server and client and NOT allow either the server or client win?

I get a pk conflict error when I insert a new row into both the server and client that have the same PK id.

Any suggestions would be grateful.

Thanks

Answer 1

General principle for this type of problem is to have a column for a natural key. The idea is that the primary key uniquely identifies a row in a table, while the natural key uniquely identifies an object that the row describes. In case of a conflict, let the server win and insert a new row on the client side. New PK will be issued, but the natural key stays the same. Later it is easy to find those rows by ordering by the natural key.

Answer 2

This is solved by using a GUID for the PK instead of a auto-incrementing int.

This guarantees that each row inserted by the server or any client will be unique and works very well.

There are some disadvantages such as additional storage space and time it takes to search etc but this is something I am willing to live with (and I will filter the data that the client gets which will help to improve performance).

Thanks everyone for your help.