With MYSQL MYISAM, we can define composite primary keys with two columns, and one column is auto_increment.
when defined like below:
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;
the id is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix.
+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 1 | dog |
| mammal | 2 | cat |
| mammal | 3 | whale |
| bird | 1 | penguin |
| bird | 2 | ostrich |
+--------+----+---------+
but when defined as
CREATE TABLE animals (
grp ENUM('fish','mammal','bird') NOT NULL,
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id,grp)
) ENGINE=MyISAM;
the id will be generated by sequence.
+--------+----+---------+
| grp | id | name |
+--------+----+---------+
| fish | 1 | lax |
| mammal | 2 | dog |
| mammal | 3 | cat |
| mammal | 4 | whale |
| bird | 5 | penguin |
| bird | 6 | ostrich |
+--------+----+---------+
there is one description in the manual
http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html
---------------quote----------------------
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. For example, if the animals table contained indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would ignore the PRIMARY KEY for generating sequence values. As a result, the table would contain a single sequence, not a sequence per grp value.
------------------quote---------------------------
But I am not quite understand of above words.
the manual describe the first create table situation as "the AUTO_INCREMENT column is part of a multiple-column index".
then for the other create table, it should be "the AUTO_INCREMENT column is part of multiple indexes".
what is the difference of the two descriptions(part of a multiple-column index) and (part of multiple indexes)?