Microsoft Sync Framework unique index error

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.

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

https://docs.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.