What MySQL returns when we alter AUTO_INCREMENT value which is less than current sequence number?

When we use AUTO_INCREMENT on a MySQL column, the sequence number always increases in ascending order starting from the default value 1 or from the value we specify.

That is the reason, MySQL does not allow changing the AUTO_INCREMENT value to a value which is less than the current sequence number. It can be understood with the help of the following example −

Example

In this example suppose we have a table named ‘emp1’ and while creating the table we specify the AUTO_INCREMENT VALUE to 100. Hence after inserting the values in table, the sequence would start from 100 onwards as can be seen from the output of following query −

mysql> Select * from emp1;
+-----+---------+
| id | NAME     |
+-----+---------+
| 100 | Sohan   |
| 101 | Harshit |
+-----+---------+
2 rows in set (0.00 sec)

Now when we try to change AUTO_INCREMENT value to 90 with the help of ALTER TABLE query, MySQL returns no error and warning because the query is alright but when we insert new values in the table, MySQL compares the specified AUTO_INCREMENT value with a current sequence number. As the specified AUTO_INCREMENT value (90) is less than current sequence number (101) MySQL start accumulating new values from 102 onwards which can be observed from following queries −

mysql> ALTER TABLE EMP1 AUTO_INCREMENT = 90;
Query OK, 2 rows affected (0.31 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> Insert into emp1(name) values('Aryan');
Query OK, 1 row affected (0.08 sec)

mysql> Select * from emp1;
+-----+---------+
| id  | NAME    |
+-----+---------+
| 100 | Sohan   |
| 101 | Harshit |
| 102 | Aryan   |
+-----+---------+
3 rows in set (0.00 sec)

In contrast, if we will try to change AUTO_INCREMENT to the value which is higher than the current sequence number then MySQL will start accumulating new values from the specified value.

To make it more clear in table ‘emp1’ we change AUTO_INCREMENT value to 108, higher than current sequence number, hence MySQL start accumulating the new inserted values from specified AUTO_INCREMENT value i.e. from 108 onwards.

mysql> ALTER TABLE EMP1 AUTO_INCREMENT = 108;
Query OK, 3 rows affected (0.30 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> Insert into emp1(name) values('Daksh');
Query OK, 1 row affected (0.04 sec)

mysql> Insert into emp1(name) values('Yashraj');
Query OK, 1 row affected (0.06 sec)

mysql> Select * from emp1;
+-----+---------+
| id  | NAME    |
+-----+---------+
| 100 | Sohan   |
| 101 | Harshit |
| 102 | Aryan   |
| 108 | Daksh   |
| 109 | Yashraj |
+-----+---------+
5 rows in set (0.00 sec)

Nitya Raut

Updated on 20-Jun-2020 07:49:38

• Related Questions & Answers
• Set auto increment initial value for MySQL table using ALTER command
• What MySQL ELT() function returns if we the index number, provided as an argument, is less than 1?
• Changing the current count of an Auto Increment value in MySQL?
• Compare date when the AdmissionDate is less than the current date in MySQL
• Display auto increment user id sequence number to begin from 001 in MySQL?
• How to change auto increment number in MySQL?
• What MySQL returns if we provide value larger than 255 as argument to MySQL CHAR() function?
• What MySQL returns if we convert an empty hexadecimal value to a number?
• How to set initial value and auto increment in MySQL?
• What MySQL returns when we remove all the columns from a table by using ALTER TABLE command with DROP keyword?
• How to auto-increment value of tables to lower value in MySQL?
• MySQL query to set my auto increment column ( id ) to zero or reset the value of auto increment field?
• Change the Auto Increment counter in MySQL?
• Passing NULL to MySQL for auto increment?
• How to change auto increment number in the beginning in MySQL?