title: conditional unique constraint
body: <p>I have a situation where i need to enforce a unique constraint on a set of columns, but only for one value of a column.</p>
  
  <p>So for example I have a table like Table(ID, Name, RecordStatus).</p>
  
  <p>RecordStatus can only have a value 1 or 2 (active or deleted), and I want to create a unique constraint on (ID, RecordStatus) only when RecordStatus = 1, since I don't care if there are multiple deleted records with the same ID.</p>
  
  <p>Apart from writing triggers, can I do that?</p>
  
  <p>I am using SQL Server 2005.</p>