O'Reilly logo

MySQL Cookbook by Paul DuBois

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required