Microsoft Sync Framework unique index error

Question

I use the MS Sync Framework to sync my SQL Server instance with a local SQL CE file to make it possible working offline with my Windows app.

I use GUIDs as keys. On my table I have a unique index on 2 columns: user_id and setting_id:

usersettings table
------------------
id          PK  -> I also tried it without this column. Same result
user_id     FK  
setting_id  FK
value

Now I do the following:

I create a new record in this table in both databases - SQL Server and SQL CE with the same user_id and setting_id.

This should work and merge the data together since this can happen in real life. But I get an error when syncing saying the unique key constraint led to an error. The key pair already exists in the table.

A duplicate value cannot be inserted into a unique index. [ Table name = user_settings,Constraint name = unique_userid_settingid ]

Why can't MS sync handle that? It should not try to insert the key pair again. It should update the value if needed.

Answer 1

The issue is if you add the same key pair to different copies of the table, they get different IDs (GUIDs) as primary keys in this usersettings table.

As this is simply a many-to-many table between Users and Settings, there is no need to have that ID as a PK (or even a column at all).

Instead, just use a concatenated key of the two FKs e.g.,

CREATE TABLE [dbo].[usersettings](
    [user_id] [UNIQUEIDENTIFIER] NOT NULL,
    [setting_id] [UNIQUEIDENTIFIER] NOT NULL,
    [value] [varchar](50) NOT NULL,
    CONSTRAINT [PK_usersettings] PRIMARY KEY CLUSTERED ([user_id] ASC, [setting_id] ASC) );

Of course, include appropriate field settings (e.g., if you use VARCHARs to store the IDs) and relevant FKs.

As the rows inserted should now be identical on the two copies, it should merge fine.

If you must have a single column as a unique identifier for the table, you could make it meaningful e.g.,

• the PK (ID) becomes a varchar (72)
• it gets filled with CONCAT(user_ID, setting_id)

As the User_ID and Setting_ID are FKs, you should already have them generated so concatenating them should be easy enough.

Answer 2

Do you get the error during sync, then it should appear as a conflict, that you must solve in code.

https://learn.microsoft.com/en-us/previous-versions/sql/synchronization/sync-framework-2.0/bb734542(v=sql.105)

I also see this in the manual: By default, the following objects are not copied to the client database: FOREIGN KEY constraints, UNIQUE constraints, DEFAULT constraints, and the SQL Server ROWGUIDCOL property. This indicates poor support for your scenario

I suggest you remove the unique constraint from the device table.