EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL AUTO_INCREMENT
 

MySQL AUTO_INCREMENT

Payal Udhani
Article byPayal Udhani
EDUCBA
Reviewed byRavi Rathore

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

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 Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW