MySQL AUTO_INCREMENT

Introduction to MySQL AUTO_INCREMENT

Mysql provides us with an attribute named AUTO_INCREMENT that is basically a sequence maintained against the column of the table for which that attribute is used. This attribute proved to be of immense help when we want to generate the unique identifier values for certain columns. AUTO_INCREMENT attribute is mostly used for the columns which we want to treat as the primary key. As the primary key column must be unique for identification purposes, 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. A sequence generates the integers in ascending order and is mostly used to get the unique numbers that are further used for identification purposes. For example, book id in the library table, task id to store the tasks or processes, etc. We can use the AUTO_INCREMENT attribute to automatically create a sequence in MySQL for a particular column of the table. This column is most often the column on which the primary key is defined. There are some of the points that you should know about the AUTO_INCREMENT property/attribute that are listed below –

• There can be only one column in a particular table that can be declared and assigned the AUTO_INCREMENT attribute.
• The data type of the column to which the AUTO_INCREMENT attribute is assigned is mostly integer.
• The column which is assigned AUTO_INCREMENT attribute needs to be a key/indexed column. This key can be either a primary key or a unique index key.
• Whenever a column is assigned the AUTO_INCREMENT attribute, NOT NULL constraint is implicitly assigned to that column as it is necessary for the AUTO_INCREMENT column to have not null constraint.

How does the AUTO_INCREMENT attribute column work?

The value of the AUTO_INCREMENT column always begins with 1 and then it is incremented by 1 whenever a new row is inserted with AUTO_INCREMENT column value as null or skipping the insertion of this column while inserting a new record in that table. If we want to fetch the last maximum sequence value generated by the AUTO_INCREMENT field then we can use the method LAST_INSERT_ID() function.

The value generated by the sequence of the AUTO_INCREMENT field is unique for that table across the sessions. Hence, the same value is retrieved by LAST_INSERT_ID() function in the current session and also in the new session if no value is inserted in the previous session after getting the value of LAST_INSERT_ID() function in the previous session.

If we try to insert the record by specifying the value of the column which is AUTO_INCREMENTed there will be two possible cases. 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. In case is the value already exists in the table for the AUTO_INCREMENT column an error will be issued saying column with that value of AUTO_INCREMENT column already exists in the table.

If we try to update the value of the AUTO_INCREMENT column there will be two cases. Firstly, if the value that we are updating is present in the table then it will issue the error saying the duplicate-key error for the column which is declared as AUTO_INCREMENT by us as it also has an implicit unique index.

Secondly, if the value that we are updating for that column does not exist in the table then that value will be updated for the row and the sequence value will be set to the current value that 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.

Whenever we delete the row in the table that was last inserted then the next value that we will get for the auto-incremented column will not be the same as the value that we deleted. Whether the deleted value will be used or not usually depends upon the storage engine of the table that we have created.

For MyISAM and InnoDB engines, the deleted row value of AUTO_INCREMENT is usually not used and the new row is entered with a value that is equal to deleted row id + 1. For example, if we deleted the maximum/last inserted row with id column 15 then the next value that we will insert will have value 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

below are some examples mentioned:

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 us insert one row without mentioning the value of id column that is assigned with AUTO_INCREMENT attribute using the following statement:

Code:

INSERT INTO educba_autoincrement(description) VALUES('sample 1');

Output:

Example #3

Let us select the record from the educba_autoincrement table and check the value of id column that got 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 see that the auto-incremented value got inserted. Let us insert the 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 new value and retrieving the output of the record is:

Code:

INSERT INTO educba_autoincrement(description) VALUES('sample 3');

Output:

We can see the sequence value is set as per inserted value 5 in the previous query.

Conclusion

We can use the AUTO_INCREMENT attribute to generate the unique values that are incremented by 1 to the column in the table. Note that one table can contain only one column with the AUTO_INCREMENT attribute in MySQL.

Recommended Articles

This is a guide to MySQL AUTO_INCREMENT. Here we discuss an introduction to MySQL AUTO_INCREMENT, sequence, how does it work, with query examples. You can also go through our other related articles to learn more –

1. LIKE in MySQL
2. Conditions in MySQL
3. Views in MySQL
4. MySQL Queries