Generating and Using Sequences - MySQL - page 451

• AUTO_INCREMENT columns must be indexed. Normally, because a sequence column
exists to provide unique identifiers, you use a PRIMARY KEY or UNIQUE index to
enforce uniqueness. Tables can have only one PRIMARY KEY , so if the table already
has some other PRIMARY KEY column, you can declare an AUTO_INCREMENT column
to have a UNIQUE index instead:
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
UNIQUE (id)
When you create a table that contains an AUTO_INCREMENT column, it's also important
to consider which storage engine to use (InnoDB, MyISAM, and so forth). The engine
affects behaviors such as reuse of values deleted from the top of the sequence (see
Recipe 13.3 ).
13.3. The Effect of Row Deletions on Sequence Generation
Problem
You want to know what happens to a sequence when you delete rows from a table that
contains an AUTO_INCREMENT column.
Solution
It depends on which rows you delete and on the storage engine.
Discussion
We have thus far considered how MySQL generates sequence values in an AUTO_INCRE
MENT column under circumstances where rows are only added to a table. But it's unre‐
alistic to assume that rows will never be deleted. What happens to the sequence then?
Refer again to Junior's bug-collection project, for which you currently have an insect
table that looks like this:
mysql> SELECT * FROM insect ORDER BY id;
+----+-------------------+------------+------------------+
| id | name | date | origin |
+----+-------------------+------------+------------------+
| 1 | housefly | 2014-09-10 | kitchen |
| 2 | millipede | 2014-09-10 | driveway |
| 3 | grasshopper | 2014-09-10 | front yard |
| 4 | stink bug | 2014-09-10 | front yard |
| 5 | cabbage butterfly | 2014-09-10 | garden |
| 6 | ant | 2014-09-10 | back yard |
| 7 | ant | 2014-09-10 | back yard |
| 8 | termite | 2014-09-10 | kitchen woodwork |
+----+-------------------+------------+------------------+