Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Working with AUTO_INCREMENT Columns in MySQL
Let us understand how to work with AUTO_INCREMENT columns in MySQL −
The AUTO_INCREMENT attribute is used to generate a unique identify for new rows. Let us see how this statement works. Before that, consider the below query −
Query
CREATE TABLE tableName (
id MEDIUMINT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO tableName (name) VALUES
(‘val1’),('val2'),('val3'),
('val4');
SELECT * FROM tableName;Output
+----+---------+ | id | name | +----+---------+ | 1 | val1 | | 2 | val2 | | 3 | val3 | | 4 | val4 | +----+---------+
In the above query, no value was specified for the ‘AUTO_INCREMENT’ column, hence MySQL assigned a sequence of numbers automatically to the ‘id’ column. A value of 0 can also be explicitly assigned so that the number sequence begins from 0. This can be done only if ‘NO AUTO VALUE ON ZERO’ SQL mode is not enabled.
If a column is declared as ‘NOT NULL’, it is possible to assign NULL to that column to generate a sequence of numbers.
When any value is inserted into an AUTO_INCREMENT column, the column gets set to that value, and the sequence also gets reset so that it generates values automatically, in the sequential range from largest column value.
An existing ‘AUTO_INCREMENT’ column can be updated that will reset the ‘AUTO_INCREMENT’ sequence as well. The most recent auto-generated ‘AUTO_INCREMENT; value can be retrieved using the ‘LAST_INSERT_ID()’ function in SQL or using the ‘mysql_insert_id()’ which is a C API function.
The following rules need to be followed while using the AUTO_INCREMENT attribute −
Every table has only one AUTO_INCREMENT column whose data type would be an integer typically.
The AUTO_INCREMENT column needs to be indexed. This means it can either be a PRIMARY KEY or a UNIQUE index.
The AUTO_INCREMENT column must have a NOT NULL constraint on it.
When the AUTO_INCREMENT attribute is set to a column, MySQL automatically adds the NOT NULL constraint to the column on its own.
94 Views
