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?
Original question:
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'
ENGINE='InnoDB'
in the code?Postgre
. Fixed the typos. And of course you can have all sorts of multi-column indices or primary keys in Postgres.