How to Reset Auto Increment in MySQL

1. Overview

MySQL relational database supports a table column property or feature called auto-increment. We use the auto-increment feature to generate a unique, ascending sequence of integer values for a column automatically. The objective is to provide each table row a unique identifier without a developer having to do the same.

In this tutorial, we’ll learn about the different options for resetting an auto-increment sequence of values in MySQL.

2. How to Use the Auto-Increment Feature?

We specify an auto-increment column using the AUTO_INCREMENT keyword. Therefore, when we define a column using the AUTO_INCREMENT keyword we can add rows of data without including a value for the auto-increment column. MySQL generates a sequence of integer values, starting with 1 by default. It keeps track of the current maximum value for the auto-incremented column using an auto-increment sequence counter.

Furthermore, we can specify only one column in a table to be the auto-increment column, and it must be a key (PRIMARY KEY, UNIQUE, KEY/INDEX).

We can reset the auto-increment counter using one of several options when we want to start a new sequence of auto-incremented values. Some reasons to reset an auto-increment sequence include demarcating a set of rows, keeping a set of column values for later use, and excluding a set of values.

It’s important to understand that “reset” when used for an auto-increment sequence has a different meaning than when used in a different context such as resetting binary logs. Resetting binary logs deletes all binary logs. Resetting an auto-increment sequence doesn’t delete the existing sequence of values. It only starts a new auto-increment sequence from an integer value that’s greater than the current auto-increment sequence’s maximum value.

3. Setting Up a Schema and Table

To demonstrate, we’ll use tables from the University database. To start with, let’s create a table with an auto-increment column using the existing table department.

CREATE TABLE department_copy 
TABLE department;

DELETE 
FROM department_copy;

ALTER TABLE department_copy 
MODIFY COLUMN id INT UNIQUE NULL AUTO_INCREMENT;

When we add data to the department_copy table, we use the auto-increment feature to generate and add values for the id column:

INSERT INTO department_copy(name,code) 
VALUES 
("Computer Science","CS"),
("Electronics and Communications","EC"),
("Mechanical Engineering","ME"),
("Civil Engineering","CE"),
("Mathematics","MA");

Let’s query the table:

SELECT * from department_copy;
+----+--------------------------------+------+
| id | name                           | code |
+----+--------------------------------+------+
|  1 | Computer Science               | CS   |
|  2 | Electronics and Communications | EC   |
|  3 | Mechanical Engineering         | ME   |
|  4 | Civil Engineering              | CE   |
|  5 | Mathematics                    | MA   |
+----+--------------------------------+------+

Indeed, the id column values are a sequence of integers starting with 1. We’ll use this data set as the basis to demonstrate different methods of resetting the auto-increment sequence.

4. Resetting the Auto-Increment with ALTER TABLE Statement

One of the options to reset an auto-increment sequence is to use the SQL statement ALTER TABLE. Let’s continue with the example table and data. We again use the AUTO_INCREMENT keyword. Let’s reset the auto-increment sequence to 10:

ALTER TABLE department_copy AUTO_INCREMENT = 10;

When we add new rows of data it uses the new auto-increment sequence. Let’s add some more example data using auto-increment:

INSERT INTO department_copy(name,code) 
VALUES 
("Computer Engineering","CE"),
("Electronics","E"),
("Information Sciences","IS");

Accordingly, it should use the auto-increment counter reset to 10 to add new id column values. Let’s verify with a query:

SELECT * FROM department_copy;
+----+--------------------------------+------+
| id | name                           | code |
+----+--------------------------------+------+
|  1 | Computer Science               | CS   |
|  2 | Electronics and Communications | EC   |
|  3 | Mechanical Engineering         | ME   |
|  4 | Civil Engineering              | CE   |
|  5 | Mathematics                    | MA   |
| 10 | Computer Engineering           | CE   |
| 11 | Electronics                    | E    |
| 12 | Information Sciences           | IS   |
+----+--------------------------------+------+

Indeed, it starts a new auto-increment sequence from 10. We can verify the auto-increment sequence using the LAST_INSERT_ID() function. This function returns the first automatically generated value added to an AUTO_INCREMENT column as a result of the last INSERT statement:

SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|               10 |
+------------------+

Notably, we can add rows of data with explicit id column values to fill the gap created by the new sequence:

INSERT INTO department_copy(id,name,code) 
VALUES (6,"Data Science","DS");

INSERT INTO department_copy(id,name,code) 
VALUES (7,"Information Technology","IT");

However, explicitly adding an id value lower than the current maximum auto-increment counter value doesn’t reset the auto-increment sequence to the lower value. Let’s demonstrate by adding more data without providing id values and thus using the auto-increment feature:

INSERT INTO department_copy(name,code) 
VALUES 
("Software Engineering","SE"),
("Electrical Engineering","EE");

Let’s query the table again:

SELECT * FROM department_copy;
+----+--------------------------------+------+
| id | name                           | code |
+----+--------------------------------+------+
|  1 | Computer Science               | CS   |
|  2 | Electronics and Communications | EC   |
|  3 | Mechanical Engineering         | ME   |
|  4 | Civil Engineering              | CE   |
|  5 | Mathematics                    | MA   |
|  6 | Data Science                   | DS   |
|  7 | Information Technology         | IT   |
| 10 | Computer Engineering           | CE   |
| 11 | Electronics                    | E    |
| 12 | Information Sciences           | IS   |
| 13 | Software Engineering           | SE   |
| 14 | Electrical Engineering         | EE   |
+----+--------------------------------+------+

Indeed, the new values are added continuing with the auto-increment sequence that we established using the ALTER TABLE statement. This is because the explicit id column values we added in between don’t reset the auto-increment; they only add individual id values.

5. Resetting the Auto-Increment with INSERT Statement

The second option to reset the auto-increment sequence is to use the INSERT statement. Using the example data set with 5 rows of data, we can reset the auto-increment sequence to 10 by adding a row of data that includes an explicit id column value of 10:

INSERT INTO department_copy(id,name,code) 
VALUES (10,"Computer Engineering","CE");

Afterward, let’s add two more rows of data using the auto-increment feature; without providing an explicit id value:

INSERT INTO department_copy(name,code) 
VALUES 
("Electronics","E"),
("Information Sciences","IS");

This adds two rows of data using the new auto-increment sequence starting with 10. Accordingly, the new automatically generated id values are 11 and 12:

SELECT * FROM department_copy;
+----+--------------------------------+------+
| id | name                           | code |
+----+--------------------------------+------+
|  1 | Computer Science               | CS   |
|  2 | Electronics and Communications | EC   |
|  3 | Mechanical Engineering         | ME   |
|  4 | Civil Engineering              | CE   |
|  5 | Mathematics                    | MA   |
| 10 | Computer Engineering           | CE   |
| 11 | Electronics                    | E    |
| 12 | Information Sciences           | IS   |
+----+--------------------------------+------+

As with resetting auto-increment using the ALTER TABLE statement, adding rows of data with id values 6 to 9 doesn’t reset the auto-increment sequence. When we delete rows of data with column values generated automatically using the auto-increment feature, it doesn’t reset the auto-increment sequence. To demonstrate, let’s delete the row of data with id 12:

DELETE FROM department_copy
WHERE id=12;

Afterward, let’s add a new row of data using auto-increment:

INSERT INTO department_copy(name,code) 
VALUES 
("Data Science","DS");

It increments the auto-increment sequence from the maximum counter value of 12 rather than reusing the deleted id value:

SELECT * FROM department_copy;
+----+--------------------------------+------+
| id | name                           | code |
+----+--------------------------------+------+
|  1 | Computer Science               | CS   |
|  2 | Electronics and Communications | EC   |
|  3 | Mechanical Engineering         | ME   |
|  4 | Civil Engineering              | CE   |
|  5 | Mathematics                    | MA   |
| 10 | Computer Engineering           | CE   |
| 11 | Electronics                    | E    |
| 13 | Data Science                   | DS   |
+----+--------------------------------+------+

Let’s remember that we can reset an auto-increment sequence only to a value higher/greater than the current maximum value of the sequence counter.

6. Resetting the Auto-Increment with UPDATE Statement

The third option to reset the auto-increment sequence is to use the UPDATE statement. Let’s again use the example data set with 5 rows of data. We can reset the auto-increment sequence to 10, for example, by updating the id 5 to 10:

UPDATE department_copy 
SET id=10 WHERE code="MA";

Afterward, let’s add a row of data using the auto-increment feature; without including an id:

INSERT INTO department_copy(name,code) 
VALUES 
("Data Science","DS");

This adds the new row of data using the new auto-increment sequence starting with 10. Accordingly, the new automatically generated id value is 11:

SELECT * FROM department_copy;
+----+--------------------------------+------+
| id | name                           | code |
+----+--------------------------------+------+
|  1 | Computer Science               | CS   |
|  2 | Electronics and Communications | EC   |
|  3 | Mechanical Engineering         | ME   |
|  4 | Civil Engineering              | CE   |
| 10 | Mathematics                    | MA   |
| 11 | Data Science                   | DS   |
+----+--------------------------------+------+

As with resetting auto-increment with the other two options, we can’t reset the auto-increment to a value lower than its current maximum value.

7. Conclusion

In this article, we learned about resetting the auto-increment sequence in MySQL. We can use any of three SQL statements: ALTER TABLE, INSERT, and UPDATE. Importantly, we can only reset the auto-increment sequence to a value higher than its current maximum value.