EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL AUTO_INCREMENT
Secondary Sidebar
MySQL Tutorial
  • Functions
    • MySQL Function
    • MySQL Aggregate Function
    • MySQL String functions
    • MySQL Date Functions
    • MySQL Window Functions
    • MySQL Math Functions
    • MySQL Boolean
    • Cursor in MySQL
    • Condition in MySQL
    • MySQL BETWEEN
    • Insert in MySQL
    • MySQL IFNULL()
    • MySQL TIMESTAMPDIFF()
    • MySQL COALESCE()
    • MySQL count()
    • MIN() in MySQL
    • MySQL Numeric
    • MySQL field()
    • MySQL FIND_IN_SET()
    • MySQL avg()
    • MySQL MAX() Function
    • MySQL BIN()
    • MySQL Concat
    • MySQL DECODE()
    • MySQL REGEXP_REPLACE()
    • MySQL Asynchronous
    • MySQL innodb_buffer_pool_size
    • MySQL key_buffer_size
    • MySQL TRUNCATE()
    • MySQL ROW_NUMBER()
    • NOT in MySQL
    • MySQL IN Operator
    • LIKE in MySQL
    • ANY in MySQL
    • MySQL NOT IN
    • MySQL CHECK Constraint
    • MySQL DISTINCT
    • MySQL ALL
    • MySQL Union
    • MySQL UNION ALL
    • MySQL EXISTS
    • MySQL ON DELETE CASCADE
    • MySQL REGEXP
    • MySQL Index
    • MySQL Add Index
    • MySQL REINDEX
    • MySQL UNIQUE INDEX
    • MySQL Clustered Index
    • MySQL? InnoDB Cluster
    • Table in MySQL
    • ALTER TABLE MySQL
    • MySQL Temporary Table
    • MySQL Clone Table
    • MySQL Repair Table
    • MySQL Lock Table
    • MySQL Optimize Table
    • TRUNCATE TABLE MySQL
    • MySQL Table Dump
    • MySQL Update Set
    • MySQL ALTER TABLE Add Column
    • MySQL RANK()
    • MySQL CTE
    • MySQL LAG()
    • MySQL GROUP_CONCAT()
    • MySQL EXTRACT()
    • MySQL REPLACE
    • MySQL AUTO_INCREMENT
    • MySQL SYSDATE()
    • MySQL NULLIF()
    • MySQL Substring
    • MySQL SUBSTRING_INDEX()
    • MySQL LOWERCASE
    • MySQL Row
    • MySQL NOW
    • MySQL CEIL
    • MySQL Alias
    • MySQL Trigger
    • MySQL SHOW Triggers
    • MySQL UPDATE Trigger
    • MySQL DELETE Trigger
    • MySQL AFTER UPDATE Trigger
    • MySQL Stored Procedure
    • ROLLUP in MySQL
    • MySQL? INSTR()
    • MySQL Subquery
    • MySQL Timestamp
    • MySQL? Hour()
    • MySQL MOD()
    • MySQL DATE_FORMAT()
    • ALTER Column in MySQL
    • MySQL Rename Column
    • MySQL Interval
    • MySQL CURDATE
    • MySQL BIT
    • MySQL Binlog
    • MySQL Average
    • MySQL TEXT
    • MySQL SHOW
    • MySQL Offset
    • MySQL Timezone
    • mysql_real_escape_string
    • MySQL Datetime
    • MySQL DATE_SUB()
    • MySQL FULLTEXT
    • MySQL DATE_ADD()
    • MySQL sum()
    • MySQL Merge
    • MySQL BigInt
    • MySQL ROUND
    • MySQL VARCHAR
    • MySQL Decimal
    • MySQL Limit
    • MySQL today()
    • MySQL WEEKDAY
    • MySQL Split
    • MySQL Create Function
    • MySQL BLOB
    • MySQL encode()
    • MySQL Primary Key
    • MySQL Foreign Key
    • Unique Key in MySQL
    • MySQL Drop Foreign Key
    • MySQL DROP TRIGGER
    • MYSQL Database
    • Delete Database MySQL
    • MySQL Root
    • MySQL Root Password
    • MySQL Client
    • MySQL Users
    • MySQL?User Permissions
    • MySQL add user
    • MySQL List User
    • MySQL Show Users
    • MySQL User Password
    • MySQL?Cardinality
    • MySQL Workbench
    • MySQL Backup
    • MySQL REVOKE
    • MySQL Dump
    • MySQL Cluster
    • MySQL Partitioning
    • MySQL Full Text Search
    • MySQL Admin Tool
    • MySQL Export Database
    • MySQL Export to CSV
  • Basic
    • MySQL floor
    • MySQL DESCRIBE table
    • MySQL encryption
    • Introduction to MySQL
    • Is SQL Server a Database?
    • What is MySQL
    • Is MySQL Programming Language
    • MySQL Server
    • MySQL AB
    • MySQL Community Server
    • How To Install MySQL
    • MySQL Versions
    • MySQL OpenSource
    • MySQL GUI Tools
    • MySQL Grant
    • MySQL Error 1064
    • MySQL Drop Database
    • MySQL not equal
    • MySQL SELECT INTO Variable
    • MySQL Commands
    • MySQL Operators
    • What is MySQL Schema
    • Wildcards in MySQL
    • MySQL Constraints
    • MySQL Administration
    • MySQL Data Type
    • MYSQL COMMIT
    • MySQL FORMAT
    • Timestamp to Date in MySQL
    • MySQL DATEDIFF
    • MySQL?Incremental Backup
    • MySQL JSON Data Type
    • MySQL ENUM
    • MySQL Default Port
    • Cheat Sheet MySQL
  • Queries
    • MySQL Queries
    • MySQL Query Commands
    • SELECT in MySQL
    • MySQL INSERT IGNORE
    • MySQL having
    • ORDER BY in MySQL
    • MySQL Cheat Sheet
    • MySQL ORDER BY Random
    • MySQL ORDER BY DESC
    • MySQL GROUP BY
    • MySQL GROUP BY Count
    • MySQL GROUP BY month
    • MySQL WHERE Clause
    • MySQL WITH
    • MySQL FETCH
    • MySQL DDL
    • MySQL DML
    • MySQL WHERE IN Array
    • MySQL Fetch Array
    • MySQL ISNULL
    • MySQL Index Types
    • Mysql? Export Schema
    • Amazon RDS for MySQL
    • MySQL greatest
  • Database
    • What is Data Modeling
    • What is Data Processing
    • DBMS Architecture
    • DBMS Keys
    • Careers in Database Administration
    • What is MySQL Database
    • MySQL Relational Database
    • How to Connect Database to MySQL
    • MySQL Database Repair
    • RDBMS Interview Questions
    • DBMS Interview Questions
  • Joins
    • Joins in MySQL
    • MySQL Outer Join
    • Left Outer Join in MySQL
    • MySQL Self Join
    • Natural Join in MySQL
    • MySQL DELETE JOIN
    • MySQL Update Join
    • MySQL Cross Join
  • Advanced
    • MySQL Formatter
    • MySQL TINYINT
    • MySQL Grant All Privileges
    • MySQL DROP TABLE
    • MySQL rename database
    • MySQL Flush Privileges
    • MySQL super Privilege
    • MySQL Character Set
    • MySQL Log File
    • MySQL Flush Log
    • Grant Privileges MySQL
    • MySQL WHILE LOOP
    • IF Statement in MySQL
    • MySQL CASE Statement
    • MySQL IF Function
    • MySQL IF EXISTS
    • MySQL UUID
    • Views in MySQL
    • MySQL Replication
    • MySQL Partition
    • Toad for MySQL
    • Navicat for MySQL
    • MySQL AES_Encrypt
    • MySQL Performance Tuning
    • MySQL Transaction
    • MySQL? sort_buffer_size
    • MySQL? Sync
    • MySQL? Query Cache
    • MySQL Collation
    • MySQL ODBC Driver
    • MySQL Partitioning
    • MySQL InnoDB
    • MySQL Float vs Decimal
    • MySQL Union vs Union All
  • Interview Questions
    • MySQL Interview Questions

Related Courses

MS SQL Certification Courses

Oracle Certification Courses

PL/SQL Certification Courses

MySQL AUTO_INCREMENT

By Payal UdhaniPayal Udhani

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.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

All in One Data Science Bundle(360+ Courses, 50+ projects)
Python TutorialMachine LearningAWSArtificial Intelligence
TableauR ProgrammingPowerBIDeep Learning
Price
View Courses
360+ Online Courses | 50+ projects | 1500+ Hours | Verifiable Certificates | Lifetime Access
4.7 (86,700 ratings)

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:

mysql auto_increment1

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:

mysql auto_increment2

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:

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 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:

value other than null

Example #7

Let us now retrieve the records:

Code:

SELECT * FROM  educba_autoincrement;

Output:

records

Example #8

After inserting new value and retrieving the output of the record is:

Code:

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

Output:

retrieving

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
Popular Course in this category
MySQL Training Program (12 Courses, 10 Projects)
  12 Online Courses |  10 Hands-on Projects |  92+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

MS SQL Training (16 Courses, 11+ Projects)4.9
Oracle Training (14 Courses, 8+ Projects)4.8
PL SQL Training (4 Courses, 2+ Projects)4.7
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • 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

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

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

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

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

EDUCBA Login

Forgot Password?

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

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

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

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

Let’s Get Started

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