Updated May 16, 2023
Introduction to MySQL AUTO_INCREMENT
In MySQL, the attribute AUTO_INCREMENT allows us to create a sequence automatically incremented for a specific column in a table. This attribute proved to be of immense help when we wanted to generate the unique identifier values for particular columns. We mostly use the AUTO_INCREMENT attribute for columns we want to treat as the primary key. As the primary key column must be unique for identification purposes, the AUTO_INCREMENT property helps to assign the auto-generated unique value incrementally for that column.
In this article, we will first learn about the sequence as the AUTO_INCREMENT attribute maintains a sequence for itself; then, we will discuss the behavior of the AUTO_INCREMENT attribute and see examples regarding the same. Further, we will see how we can reset the value for the AUTO_INCREMENT column.
Sequence and AUTO_INCREMENT attribute
Mysql sequence is a list of integers. We mostly utilize a sequence to generate integers in ascending order, which further helps us obtain unique numbers for identification purposes. For example, book id in the library table, task id to store the tasks or processes, etc. The AUTO_INCREMENT attribute allows us to automatically create a sequence in MySQL for a specific table column. In most cases, we designate this column as the primary key. Here are some points you should know about the AUTO_INCREMENT property/attribute:
- You can only designate and assign the AUTO_INCREMENT attribute to a specific table.
- The column’s data type assigned with the AUTO_INCREMENT attribute is typically an integer.
- The column assigned with the AUTO_INCREMENT attribute must be a key/indexed column. This key can be either a primary key or a unique index key.
- Assigning the AUTO_INCREMENT attribute to a column implicitly assigns a NOT NULL constraint to that column. This constraint is necessary for the AUTO_INCREMENT column to ensure it does not have null values.
How does the AUTO_INCREMENT attribute column work?
The AUTO_INCREMENT column starts with a value of one and increments by one whenever we insert a new row. This happens when we set the AUTO_INCREMENT column value as null or when we skip inserting this column while inserting a new record in the table. If we want to fetch the last maximum sequence value generated by the AUTO_INCREMENT field, we can use the LAST_INSERT_ID() function.
The value generated by the sequence of the AUTO_INCREMENT field is unique for that table across the sessions. Hence, If no value is inserted in the previous session after obtaining the value from the LAST_INSERT_ID() function, the current and new sessions retrieve the same value using the LAST_INSERT_ID() function.
When attempting to insert a record and specifying a value for a column that is set to AUTO_INCREMENT in MySQL, there are two possible scenarios: If the value that is inserted in the AUTO_INCREMENT column does not exist in the table, then MySQL will allow its insertion and the value of the sequence of that AUTO_INCREMENT column will be set to the value that we inserted and the next value that will be retrieved from that sequence will be equivalent to current inserted value +1.
There are two possible cases when attempting to insert a record by specifying the value of an AUTO_INCREMENT column. MySQL permits the value insertion in the AUTO_INCREMENT column if it does not already exist in the table. The insertion operation will set the sequence value of the AUTO_INCREMENT column to the inserted value, and the subsequent sequence value retrieved will be equal to the current inserted value +1. If the table in MySQL already contains a value for an AUTO_INCREMENT column, attempting to insert that value will generate an error indicating that a column with that value already exists for the AUTO_INCREMENT column in the table.
If we try to update the value of the AUTO_INCREMENT column, there will be two cases. Firstly, suppose the value we are updating is already present in the table. In that case, it will generate an error indicating a duplicate-key mistake for the column declared AUTO_INCREMENT, as it also has an implicit unique index.
Secondly, if the value we update for that column does not exist in the table, we will update the value for the row, and the sequence value will be set to the current value we updated. Hence, when we will insert the record next time the value of the sequence that we will get will be our updated value + 1.
When we delete the last-inserted row in the table, the next value for the auto-incremented column will not be the same as the deleted value. The usage of the deleted value depends on the storage engine of the table we create.
Typical practice for the MyISAM and InnoDB engines is to disregard the deleted row value of AUTO_INCREMENT and instead insert the new row with a value equal to the ID of the deleted row plus one. For example, if we delete the maximum/last inserted row with id column 15, the next value we will insert will be 16 instead of 15.
We can reset the value of the AUTO_INCREMENT attribute by using the ALTER command for that particular table using the below syntax –
ALTER TABLE name_of_table AUTO_INCREMENT = numerical_value;Examples to Implement MySQL AUTO_INCREMENT
Here are some examples:
Example #1
Let us create one table named educba_autoincrement using the following create table query:
Code:
CREATE TABLE educba_autoincrement (
id INT NOT NULL AUTO_INCREMENT,
description VARCHAR(45) DEFAULT NULL,
PRIMARY KEY (id)
);Output:
Example #2
Let’s insert one row into the table without specifying a value for the id column, which is assigned the AUTO_INCREMENT attribute, using the following statement:
Code:
INSERT INTO educba_autoincrement(description) VALUES('sample 1');Output:
Example #3
Let’s select the record from the educba_autoincrement table and verify the value of the id column that was inserted using the following command:
Code:
SELECT * FROM educba_autoincrement;Output:
Example #4
Let us now try inserting a null value in the id column using the following query:
Code:
INSERT INTO educba_autoincrement(id,description) VALUES(NULL,'sample 2');Output:
Example #5
Let us now retrieve the records:
Code:
SELECT * FROM educba_autoincrement;Output:
Example #6
We can observe that the system has inserted the auto-incremented value. Let us insert a value other than null:
Code:
INSERT INTO educba_autoincrement(id,description) VALUES(5,'sample 2');Output:
Example #7
Let us now retrieve the records:
Code:
SELECT * FROM educba_autoincrement;Output:
Example #8
After inserting a new value and retrieving the output of the record is:
Code:
INSERT INTO educba_autoincrement(description) VALUES('sample 3');Output:
The sequence value is set based on the inserted value 5 in the previous query.
Conclusion
The column in the table generates unique values by incrementing them by 1 using the AUTO_INCREMENT attribute. Note that one table can contain only one column with the AUTO_INCREMENT attribute in MySQL.
Recommended Articles
We hope that this EDUCBA information on “MySQL AUTO_INCREMENT” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
