Unique indexes

[iastinf]

There is a problem with the unique indexes.
Example
create table test (
i int not null primary key,
UserId int not null,
is_default bit not null)

CREATE UNIQUE INDEX [IDX_is_default] ON [dbo].[test]
(
[UserId] ASC
)
WHERE ([is_default]=(1))

insert into test (i,UserId,is_default) values (1,10,0)
insert into test (i,UserId,is_default) values (2,10,1)

I can synchronize now the destination without any problem

But if i run the follow two sqls

insert into test (i,UserId,is_default) values (3,10,0)
update test set is_default=0 where i=2
update test set is_default=1 where i=3

and try to synchronize the destination
follow error appers

2022-04-11 16:47:32.8140|INFO|Table [dbo].[test] has 2 changes
2022-04-11 16:47:32.8174|INFO|Replicating 2 changes to destination Secondary 1
2022-04-11 16:47:32.8226|DEBUG|Replicating change #1 of 2 (Version 1678578, CreationVersion 1678576)
2022-04-11 16:47:32.8255|DEBUG|Executing insert: insert into [dbo].[test] ([i], [is_default], [UserId]) values (@0, @1, @2); (@0 = 3, @1 = True, @2 = 10)
2022-04-11 16:47:32.8321|ERROR|Error replicating changes to destination Secondary 1 System.Data.SqlClient.SqlException (0x80131904): Cannot insert duplicate key row in object 'dbo.test' with unique index 'IDX_is_default'. The duplicate key value is (10).
The statement has been terminated.

If i execute all the statements together

delete from test
insert into test (i,UserId,is_default) values (1,10,0)
insert into test (i,UserId,is_default) values (2,10,1)
insert into test (i,UserId,is_default) values (3,10,0)
update test set is_default=0 where i=2
update test set is_default=1 where i=3

and synchronize the destination everything goes well

[mganss]

Thanks. This is similar to foreign keys. I'll try and fix this but will take a couple of days before I can get to it.

[mganss]

I've tried to solve this by deferring individual change replication to the version where the row was last modified. In your above example the statements executed would be:

update test set is_default=0 where i=2
insert into test (i,UserId,is_default) values (3,10,1)

[iastinf]

I don't see any usage of the new property UniqueConstraints in the synchronizer class

[mganss]

Yes, that's intended. I had originally planned on handling this in the same manner as foreign keys, i.e. by disabling/enabling the unique constraints/indexes. But this approach seems infeasible because you can't disable/enable unique constraints and indexes. But unlike foreign key constraints it should suffice to just defer replication until the latest possible time to satisfy unique constraints. I've left the UniqueConstraint class in there for possible future usage.