title: SQL Server : create unique key BUT one of the columns could have several values
body: <p>I have an <code>Accounts</code> table, and I'm having problems with duplicated accounts being created in two calls to the WS at the same time.</p>
  
  <p>What I'm trying to do is to add a unique key to the table <code>Account</code>, where <code>Identification_Type</code>, <code>Identification_Number</code> and <code>Account_Status</code> should not be duplicated. </p>
  
  <p>But the problem is, I can have several accounts with the same <code>Identification_Type</code> and <code>Identification_Number</code>, only if they have <code>Account_Status = Closed</code>, <code>Rejected</code> or <code>Expired</code>. And that's ok.</p>
  
  <p>What I can't have is more than one account if the status is <code>Created</code>, <code>Pending</code>, <code>Enabled</code>, <code>Inabilited</code>. </p>
  
  <p>I want to prevent an account to be created if there is already one with the <code>Status = Created</code>, <code>Pending</code>, <code>Enabled</code>, <code>Inabilited</code>. </p>
  
  <p>Is there a way to create a Unique Key or something in SQL with this restriction?</p>
  
  <p>I will put an example of what I want to prevent.</p>
  
  <p>Table Account:</p>
  
  <p>Identification_Type | Identification_Number | Account_Status</p>
  
  <p>---------1-----------------------12345678-----------------Closed<br>
  ---------1-----------------------12345678-----------------Closed<br>
  ---------1-----------------------12345678-----------------Rejected<br>
  ---------1-----------------------12345678-----------------Closed<br>
  ---------1-----------------------12345678-----------------Expired<br>
  ---------<strong>1</strong>-----------------------<strong>12345678</strong>-----------------<strong>Created</strong>  </p>
  
  <p>And, as there is already an <code>Account</code> in <code>Account_Status = Created</code> I want to add a Unique Key to prevent to add another one in <code>Account_Status = Created</code>, <code>Pending</code>, <code>Enabled</code> or <code>Inabilited</code>. </p>
  
  <p>I can't have more than one <code>Account</code> in <code>Account_Status = Created</code>, <code>Pending</code>, <code>Enabled</code> or <code>Inabilited</code> for the same combination of <code>Identification_Type</code> | <code>Identification_Number</code>, but I can have several for the other status.</p>
  
  <p>Thanks in advance</p>