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

Published on 19-Feb-2018 13:27:19

• Related Questions & Answers
• What MySQL ELT() function returns if we the index number, provided as an argument, is less than 1?
• Compare date when the AdmissionDate is less than the current date in MySQL
• How do you find out which sequence number was assigned recently by MySQL AUTO_INCREMENT?
• What MySQL returns if we provide value larger than 255 as argument to MySQL CHAR() function?
• Alter a MySQL column to be AUTO_INCREMENT?
• What MySQL returns if we convert an empty hexadecimal value to a number?
• What MySQL returns when we remove all the columns from a table by using ALTER TABLE command with DROP keyword?
• How can we change MySQL AUTO_INCREMENT starting number?
• MySQL ALTER column to remove primary key and auto_increment?
• 8085 program to count number of elements which are less than 0A
• Fetch rows where a field value is less than 5 chars in MySQL?
• What is Less than Operator (<) in JavaScript?
• How to select a date less than the current date with MySQL?
• MySQL query to get the next number in sequence for AUTO_INCREMENT field?
• Find Largest Special Prime which is less than or equal to a given number in C++