I have exactly the same question as posted here.
But in my case I do not use MySQL but Postgres. Can this be done with Postgres? All things indicate I can not use a combined index in Postgres, so assuming it can not: what solution would be most recommended?
I have a table that contains information on invoices of a company. However, this company has two branches, and each of them has a unique invoicing sequence; a "Serie A" and "Serie B", so to speak. However, this is one single company and I do not want to create two invoice tables. Rather, I somehow want to have two different auto-increments for one table. (...)
What I am doing right now is not using the primary key as invoice number (which would be ideal), but rather using a secondary column with the invoice id, which is incremented manually (well, using a PHP script, but it's still not automatic), by checking the latest invoice for that particular series.
This is my current setup:
CREATE TABLE `invoices` ( `id` mediumint unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY, `invoicenumber` mediumint unsigned NOT NULL, `branch` enum('A','B') NOT NULL, `date` date NOT NULL, `client` varchar(100) NOT NULL ) COMMENT='' ENGINE='InnoDB';
To check the latest invoice, I run:
SELECT MAX(invoicenumber+1) AS new_invoice_number FROM invoices WHERE branch = 'A'