title: mysql prevent redundant records with multiple fields
body: <p>i have a problem here. I have a table of the following schema:</p>
  
  <pre><code>id : int(11) not null primary key
  name : varchar(255) not null
  status : enum('ACTIVE','DELETED')
  </code></pre>
  
  <p>Note: The records are 'soft-deleted' and we just flag the status to 'DELETED'.</p>
  
  <p>The issue is that i don't want the a fella to be able to create entry of same name(redundant) via 2 different threads unless the existing record in db is in 'DELETED' state. What's the possible ways to do this? </p>
  
  <p>I cant just unique index the name + status, because if we have an item(with same name) in 'DELETED', and my want-to-be-deleted zone is 'ACTIVE', error will happened as i flag the zone to be 'DELETED'.</p>