MySQL: two-columns Primary Key with auto-increment

Everyone working with MySQL (and DBMS in general) knows about the AUTO_INCREMENT attribute that can be used on a column - often the primary key - to generate a unique identity for new rows. To make a quick example, let's take the most classic example from the MySQL online manual:

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

CREATE TABLE animals (

id MEDIUMINT NOT NULL AUTO_INCREMENT,

name CHAR(30) NOT NULL,

PRIMARY KEY (id)

) ENGINE=MyISAM;

INSERT INTO animals (name) VALUES

('dog'),('cat'),('penguin'),

('lax'),('whale'),('ostrich');

SELECT * FROM animals;

which returns:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

+----+---------+

| id | name |

+----+---------+

| 1 | dog |

| 2 | cat |

| 3 | penguin |

| 4 | lax |

| 5 | whale |

| 6 | ostrich |

+----+---------+

That said... What happens if our primary key consists of two columns and we set the auto-increment feature on one of them?

Will it reset the auto-increment counter when the other column's value changes?

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

+--------+----+---------+

| grp | id | name |

+--------+----+---------+

| fish | 1 | lax |

| mammal | 1 | dog |

| mammal | 2 | cat |

| mammal | 3 | whale |

| bird | 1 | penguin |

| bird | 2 | ostrich |

+--------+----+---------+

Or will it generate an unique ID for each row?

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  2 | dog     |
| mammal |  3 | cat     |
| mammal |  4 | whale   |
| bird   |  5 | penguin |
| bird   |  6 | ostrich |
+--------+----+---------+

+--------+----+---------+

| grp | id | name |

+--------+----+---------+

| fish | 1 | lax |

| mammal | 2 | dog |

| mammal | 3 | cat |

| mammal | 4 | whale |

| bird | 5 | penguin |

| bird | 6 | ostrich |

+--------+----+---------+

The answer is: it will depend on which table engine you're using and how you create the index in the CREATE statement. The official MySQL online manual says that:

For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix . This is useful when you want to put data into ordered groups.

Meaning that, in order to get the first result, you need to CREATE your table putting the grp column before the id column when you declare the PRIMARY KEY , just like the following:

CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

CREATE TABLE animals (

grp ENUM('fish','mammal','bird') NOT NULL,

id MEDIUMINT NOT NULL AUTO_INCREMENT,

name CHAR(30) NOT NULL,

PRIMARY KEY (grp,id)

) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES

('mammal','dog'),('mammal','cat'),

('bird','penguin'),('fish','lax'),('mammal','whale'),

('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

Needless to say, if you do the opposite you'll get the latter result instead.

A couple important things to notice:

Always remember that (as written in the MySQL online docs) AUTO_INCREMENT values are always reused if you delete the row with the biggest AUTO_INCREMENT value in any group. This happens even for MyISAM tables, for which AUTO_INCREMENT values normally are not reused. Meaning that, if you use the first scenario, this feature will hit you multiple times - which can be easily a good thing, as long as you're aware of that.
If the AUTO_INCREMENT column is part of multiple indexes, MySQL will generate sequence values using the index that begins with the AUTO_INCREMENT column, if there is one, regardless of any Primary Key group. For example, if the animals table above contained indexes PRIMARY KEY (grp, id) and INDEX (id) , MySQL would ignore the PRIMARY KEY for generating sequence values. As a result, our result would always be the latter one regardless of anything else.
IMPORTANT: If you're using this technique in a replication environment using a version of MySQL lower than 5.1.65 you might run into some consistency issues, as explained in the official docs: An INSERT into a table that has a composite primary key that includes an AUTO_INCREMENT column that is not the first column of this composite key is not safe for statement-based logging or replication. Beginning with MySQL 5.1.64, such statements are marked as unsafe. (Bug #11754117, Bug #45670). Luckily enough, this has been fixed in MySQL 5.1.65.

Unfortunately, if you run InnoDB, there's no way to do that by the book: the online documentation clearly says that any InnoDB table with an AUTO_INCREMENT column requires at least one key where the auto-increment column is the only or leftmost column. If you need to replicate a similar behaviour in an InnoDB environment you can take advantage of the InnoDB's clustered composite index feature as explaining in this StackOverflow entry and see if it can work for your own scenario.

That's about it: happy coding!