title: How can I prevent duplication without primary key in mysql
body: <p>I have a table called <code>INVOICES</code> that receives entries from a PHP script. It has many columns, but the two most relevant are <code>INVOICE_ID</code> and <code>INVOICE_TYPE</code>. Basically the <code>INVOICE_TYPE</code> is a number from <strong>0</strong> to <strong>3</strong>, which designates different types of invoices.</p>
  
  <p>Up to this point, everything ran smoothly until two users submitted invoices while the server had a hiccup and wrote both in as the same <code>INVOICE_ID</code>. The reason for this is the PHP script reads the MAX <code>INVOICE_ID</code> of the <code>INVOICE_TYPE</code>, then adds <strong>1</strong>, then inserts the new row with that <code>INVOICE_ID</code>. In essence, it is programmatically a <code>primary key</code>. <strong>99.9%</strong> of the time it worked, but that one time it was a problem. </p>
  
  <p>I have tried finding SQL solutions but do not have sufficient knowledge of it. I have tried doing it myself in an SQL query to read the MAX, increment, and the insert but just throws an exception that you cannot select and insert from the same table at once.</p>
  
  <p>What I'm wondering is if there is an auto-increment that could be conditional to the <code>INVOICE_TYPE</code>, to only increment if the type is matched. Any suggestions would help at this point.</p>