title: Merge Replication Unique Key Constraint
body: <p>I have SQL Server Merge Replication setup on two servers and I am getting a key constraint error when the synchronization runs.  The only way I can resolve this issue is to delete the record on one of the servers and then run synchronization.  </p>
  
  <p><strong>Question:</strong> Is there a way to configure replication or a resolver so that the publisher record wins and the subscriber record is automatically removed when it encounters a unique or primary key violation?</p>
  
  <p>Sample Table:</p>
  
  <pre><code>CREATE TABLE [dbo].[tblPeople](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [col1] [int] NULL,
    [col2] [int] NULL,
    [col3] [int] NULL,
    [name] [varchar](52) NULL 
  CONSTRAINT [UK_keys] UNIQUE NONCLUSTERED 
  (
    [col1] ASC,
    [col2] ASC,
    [col3] ASC
  )
  </code></pre>
  
  <p>Insert on Server 1</p>
  
  <pre><code>INSERT into tblPeople (col1, col2, col3, name) values (1, 1, 1, 'Server 1 Insert')
  </code></pre>
  
  <p>Insert on Server 2</p>
  
  <pre><code>INSERT into tblPeople (col1, col2, col3, name) values (1, 1, 1, 'Server 2 Insert')
  </code></pre>
  
  <p>Trigger synchronization, which results in this conflict error and both servers having their own version of this record.</p>
  
  <p><em>A row insert at 'SERVER1.TestDb' could not be propagated to 'SERVER2.TestDb'. This failure can be caused by a constraint violation.  Violation of UNIQUE KEY constraint 'UK_keys'. Cannot insert duplicate key in object 'dbo.tblPeople'. The duplicate key value is (1, 1, 1).</em></p>
  
  <p>Everything I read about this suggests adding a unique guid or using identity columns, which isn't a solution to this problem.  The identity ranges work great and I can even create my own rowguid, but that still doesn't solve the constraint violation where I end up needing to manually delete the records.</p>
  
  <p>This person asked a similar question, but I need the unique key on top of the guid and identity.
  <a href="https://stackoverflow.com/questions/5964062/automatically-resolve-primary-key-merge-conflict">Automatically resolve primary key merge conflict</a></p>