
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
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)
- Related Articles
- 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
- Alter a MySQL column to be AUTO_INCREMENT?
- 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?
- What MySQL returns when we remove all the columns from a table by using ALTER TABLE command with DROP keyword?
- How do you find out which sequence number was assigned recently by MySQL AUTO_INCREMENT?
- How can we change MySQL AUTO_INCREMENT starting number?
- MySQL ALTER column to remove primary key and auto_increment?
- MySQL query to get the next number in sequence for AUTO_INCREMENT field?
- What happens when I insert the value ‘NULL’ in an AUTO_INCREMENT MySQL column?
- On inserting ‘NULL’, ‘0’ or No Value to the column, will MySQL assign sequence number for AUTO_INCREMENT column?
- How do I get the current AUTO_INCREMENT value for a table in MySQL?
- What MySQL returns when we use DISTINCT clause with the column having multiple NULL values?
- How to select a date less than the current date with MySQL?
