How can I prevent duplication without primary key in mysql

I have a table called INVOICES that receives entries from a PHP script. It has many columns, but the two most relevant are INVOICE_ID and INVOICE_TYPE. Basically the INVOICE_TYPE is a number from 0 to 3, which designates different types of invoices.

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 INVOICE_ID. The reason for this is the PHP script reads the MAX INVOICE_ID of the INVOICE_TYPE, then adds 1, then inserts the new row with that INVOICE_ID. In essence, it is programmatically a primary key. 99.9% of the time it worked, but that one time it was a problem.

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.

What I’m wondering is if there is an auto-increment that could be conditional to the INVOICE_TYPE, to only increment if the type is matched. Any suggestions would help at this point.

Answer

If you insert only one row to one table at once simplest solution is to apply unique index on both columns.

CREATE UNIQUE INDEX invoice_id_type_unique
ON INVOICES(INVOICE_ID,INVOICE_TYPE);

But if you execute more queries based on the same data you need to use transactions to prevent modifying/inserting only part of data.

START TRANSACTION;
SELECT @invoice_id:=MAX(INVOICE_ID) FROM INVOICES WHERE INVOICE_TYPE=1;
INSERT INTO INVOICES (...,@invoice_id,...);
...
... #OTHER QUERIES UPDATING DATA
COMMIT;