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.

Umbilication

Is this really related to the ISO/ANSI <sql> language? – Kotz

Since it's about unique keys in SQL I'd say yes. – Umbilication

A foreign key doesn't guarantee uniqueness. – Kotz

As I mention in my question: On one table I have a unique key on 2 columns – Umbilication

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

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 – Seeseebeck

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.

Prebend

Sadly that won't work. In the beginning I had no id column. My table was just like the one you describe. – Umbilication

I added an approach for when you must have 1 ID column... changing it to a VARCHAR(72) and populating it with CONCAT(user_ID, setting_Id) – Prebend

That would probably solve the issue. But since I have a massive database I would only change it that way if I knew it is really necessary and no better way. I was hoping for a source were people were dealing with that problem. It should be a common problem with the sync framework. – Umbilication

I respect that that's a definite concern (and would probably agree in your circumstance too - I'd definitely want to explore other options). However, I'll leave this here, if for no other reason than to stop others giving you this answer. – Prebend

Of course. If I get no better solution I will do exactly what you suggest. – Umbilication

I'm sorry there are no better answers than this. But thanks for the accept. – Prebend

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.

Seeseebeck

Yes, I get an error during synchronization. But I cannot resolve it properly. All resolve actions do not really help me since I don't just get a sync conflict - I get a DB error. – Umbilication