title: NHibernate unique constraint remove if duplicate
body: <p>Is there a way to tell nHibernate to remove the duplicate value on a row's column that is uniquely constrained when updating a row with a duplicate value.</p>
  
  <p>For example (OtherId and Animal is compositely unique constrained)</p>
  
  <pre><code>Id | OtherId | Animal
  ------------
  1  | 1       | Dog
  2  | 1       | Cat
  3  | 1       | Bear
  4  | 2       | Dog
  </code></pre>
  
  <p>Updating Id 3 to Dog, should result in this</p>
  
  <pre><code>Id | OtherId | Animal
  1  | 1       | NULL
  2  | 1       | Cat
  3  | 1       | Dog
  4  | 2       | Dog
  </code></pre>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I was able to solve my problem by creating an unique index in my table</p>
  
  <pre><code>CREATE UNIQUE INDEX [Id_OtherId_Animal_Index]
  ON [dbo].[Animals] (OtherId, Animal)
  WHERE Animal IS NOT NULL;
  </code></pre>
  
  <p>This way, I prevent insertion of duplicate (1, Dog) and still allow (2, Dog). This will also allow multiple (1, NULL) to be inserted.</p>
  
  <p>Next, based on Frédéric's suggestion below, I edited my service layer to check BEFORE insertion if it will be a duplicate. If it will, then NULL the animal column of which would be uniquely constrained.</p>