How can you have two auto-incremental columns in one table? I have a MySQL 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. I know this is technically not possible, but I guess this is a problem others have tackled before, so I'd like to know if there is a well-known 'solution' for this issue? 

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 lateset invoice, I run:

SELECT MAX(invoicenumber+1) AS new_invoice_number FROM invoices WHERE branch = 'A'
What you are proposing to do can only be done with MySQL cleanly under three(3) conditions 


CONDITION #1 : Use the MyISAM storage engine
CONDITION #2 : Make auto_increment column part of a compound primary key
CONDITION #3 : Each auto_increment for a given type must exist in its own row
See the auto_increment documentation for MyISAM


Here is your original table layout

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'; 


Based on the three conditions I just mentioned, here is the new proposed table layout:

CREATE TABLE `invoices` ( 
  `invoicenumber` mediumint unsigned NOT NULL auto_increment, 
  `branch` enum('A','B') NOT NULL, 
  `date` date NOT NULL, 
  `client` varchar(100) NOT NULL,
  PRIMARY KEY (branch,invoicenumber)
) COMMENT='' ENGINE='MyISAM'; 


Here is an example via sample data and SQL:

drop database if exists user1162541;
create database user1162541;
use user1162541
CREATE TABLE `invoices` ( 
  `invoicenumber` mediumint unsigned NOT NULL auto_increment, 
  `branch` enum('A','B') NOT NULL, 
  `date` date NOT NULL, 
  `client` varchar(100) NOT NULL,
  PRIMARY KEY (branch,invoicenumber)
) COMMENT='' ENGINE='MyISAM'; 
INSERT INTO invoices (branch,date,client) VALUES
('A',DATE(NOW()),'John'),
('B',DATE(NOW()),'Jack'),
('A',DATE(NOW()),'Jeff'),
('B',DATE(NOW()),'Joel'),
('A',DATE(NOW()),'Jane'),
('B',DATE(NOW()),'Joan'),
('A',DATE(NOW()),'June');
SELECT * FROM invoices ORDER BY branch,invoicenumber;


Here it is executed:

mysql&gt; drop database if exists user1162541;
Query OK, 1 row affected (0.01 sec)

mysql&gt; create database user1162541;
Query OK, 1 row affected (0.02 sec)

mysql&gt; use user1162541
Database changed
mysql&gt; CREATE TABLE `invoices` (
    -&gt;   `invoicenumber` mediumint unsigned NOT NULL auto_increment,
    -&gt;   `branch` enum('A','B') NOT NULL,
    -&gt;   `date` date NOT NULL,
    -&gt;   `client` varchar(100) NOT NULL,
    -&gt;   PRIMARY KEY (branch,invoicenumber)
    -&gt; ) COMMENT='' ENGINE='MyISAM';
Query OK, 0 rows affected (0.00 sec)

mysql&gt; INSERT INTO invoices (branch,date,client) VALUES
    -&gt; ('A',DATE(NOW()),'John'),
    -&gt; ('B',DATE(NOW()),'Jack'),
    -&gt; ('A',DATE(NOW()),'Jeff'),
    -&gt; ('B',DATE(NOW()),'Joel'),
    -&gt; ('A',DATE(NOW()),'Jane'),
    -&gt; ('B',DATE(NOW()),'Joan'),
    -&gt; ('A',DATE(NOW()),'June');
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql&gt; SELECT * FROM invoices ORDER BY branch,invoicenumber;
+---------------+--------+------------+--------+
| invoicenumber | branch | date       | client |
+---------------+--------+------------+--------+
|             1 | A      | 2012-04-21 | John   |
|             2 | A      | 2012-04-21 | Jeff   |
|             3 | A      | 2012-04-21 | Jane   |
|             4 | A      | 2012-04-21 | June   |
|             1 | B      | 2012-04-21 | Jack   |
|             2 | B      | 2012-04-21 | Joel   |
|             3 | B      | 2012-04-21 | Joan   |
+---------------+--------+------------+--------+
7 rows in set (0.00 sec)

mysql&gt;


Give it a Try !!!

CAVEAT : At present, only the MyISAM Storage Engine supports multiple auto_increment values grouped with other columns. This is not possible with InnoDB based on auto_increment columns being tied directly to the gen_clust_index (aka Clustered Index) !!!