EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL AUTO_INCREMENT

MySQL AUTO_INCREMENT

Payal Udhani
Article byPayal Udhani
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated May 16, 2023

MySQL AUTO_INCREMENT

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

mysql auto_increment1

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:

mysql auto_increment2

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:

mysql auto_increment3

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:

inserting a null value in the id column

Example #5

Let us now retrieve the records:

Code:

SELECT * FROM  educba_autoincrement;

Output:

mysql auto_increment5

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:

value other than null

Example #7

Let us now retrieve the records:

Code:

SELECT * FROM  educba_autoincrement;

Output:

records

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:

retrieving

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.

  1. LIKE in MySQL
  2. Conditions in MySQL
  3. Views in MySQL
  4. MySQL Queries
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Data Science Bundle2000+ Hour of HD Videos | 80 Learning Paths | 400+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program2000+ Hours of HD Videos | 43 Learning Paths | 550+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more