同一个表中的两个递增列

Question

I have a table that contains invoices for several companies, each company needs to have their own incrementing invoice number system.

id | invoiceId | companyId
--------------------------
1  | 1         | 1
2  | 2         | 1
3  | 1         | 2
4  | 1         | 3

I was hoping to achieve this with a unique compound key similar to this approach for MyISAM outlined here, but it seems it is not possible with InnoDB.

I need to return the new ID immediately after insertion and have concerns about creating a race condition if I try and achieve this with PHP.

Is my best option to create a trigger and if yes what would that look like? I have no experience with triggers and my research into using an after insert trigger has me worried with this quote from the MariaDB documentation:

RESTRICTIONS

You can not create an AFTER trigger on a view. You can not update the NEW values. You can not update the OLD values.

Thanks for any advice

Answer 1

You need to add a unique index besides getting your next value. The next value is best gotten by querying the table with a trigger or by some procedure within a transaction. The remark of trigger on a view is not relevant in that case.

The unique index is on companyId,invoiceId is required to prevent two insert processes running on the same company adding an invoice, which then can end up both with the same invoiceId. Even better is when you switch to InnoDB so you can use transactions: Then 2 processes started at virtually the same time can benefit from transaction isolation with as result that they will be serialized and you get 2 unique incrementing invoice ids returned without having to handle the unique index exception in your code.