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

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.

No credit card required