MySQL Cookbook

The Effect of Record Deletions on Sequence Generation

Problem

You want to know what happens to a sequence when you delete records from a table that contains an AUTO_INCREMENT column.

Solution

It depends on which records you delete and on the table type.

Discussion

We have thus far considered how sequence values in an AUTO_INCREMENT column are generated for circumstances where records are only added to a table. But it’s unrealistic to assume that records 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          | 2001-09-10 | kitchen    |
|  2 | millipede         | 2001-09-10 | driveway   |
|  3 | grasshopper       | 2001-09-10 | front yard |
|  4 | stink bug         | 2001-09-10 | front yard |
|  5 | cabbage butterfly | 2001-09-10 | garden     |
|  6 | ant               | 2001-09-10 | back yard  |
|  7 | ant               | 2001-09-10 | back yard  |
|  8 | millbug           | 2001-09-10 | under rock |
+----+-------------------+------------+------------+

That’s about to change, because after Junior remembers to bring home the written instructions for the project, you read through them and discover two things that bear on the insect table’s contents:

• Specimens should include only insects, not other insect-like creatures such as millipedes and millbugs.

• The purpose ...