MySQL - AUTO_INCREMENT attribute

In the MySQL database, a sequence is a list of integers as unique number to produce in the ascending order. The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows.

• Column where set AUTO_INCREMENT attribute is typically primary key and it must be indexed as either primary key or unique index.
• One table can have only one AUTO_INCREMENT attribute.
• AUTO_INCREMENT attribute must have NOT NULL constraint even when you create table then MYSQL add NOT NULL by default into column.
• During INSERT MySQL assigned sequence numbers automatically into AUTO_INCREMENT attributed column.

Exercise

Create table without mentioning NOT NULL:

CREATE TABLE `employee` (

  `emp_id` int(11) AUTO_INCREMENT,

  `emp_name` varchar(50) DEFAULT NULL,

  PRIMARY KEY (`emp_id`)

);

Insert statement without mentioning emp_id col:

INSERT INTO employee(emp_name) VALUES ('John Hunt');

INSERT INTO employee(emp_name) VALUES ('Donald Trump');

INSERT INTO employee(emp_name) VALUES ('Obama');

You can explicitly assign 0 to the AUTO_INCREMENT attributed column to generate sequence number:

INSERT INTO employee(emp_id,emp_name) VALUES (0,'John Hunt');

INSERT INTO employee(emp_id,emp_name) VALUES (0, 'Donald Trump');

INSERT INTO employee(emp_id,emp_name) VALUES (0,'Obama');

You can assign NULL to the AUTO_INCREMENT attributed column to generate sequence number as column is declared is NOT NULL:

INSERT INTO employee(emp_id,emp_name) VALUES (NULL,'John Hunt');

INSERT INTO employee(emp_id,emp_name) VALUES (NULL, 'Donald Trump');

INSERT INTO employee(emp_id,emp_name) VALUES (NULL,'Obama');

You can retrieve the most recent automatically generated AUTO_INCREMENT value with the LAST_INSERT_ID().

Reset AUTO_INCREMENT

INSERT - When INSERT any other value instead 0 or NULL then sequence is reset upto that value and next auto generated value followed by sequence:

INSERT INTO employee(emp_id,emp_name) VALUES (10,'George Bush');

INSERT INTO employee(emp_id,emp_name) VALUES (NULL, 'Sheron');

ALTER – You can set value with ALTER TABLE statement like:

ALTER TABLE employee AUTO_INCREMENT = 20;

INSERT INTO employee(emp_id,emp_name) VALUES (NULL, 'Raman');

Reset after table backup and TRUNCATE data

In such use case where you need to backup table data and truncate table to make table lightweight but do not want identity column start again from 1 as truncated.

• Backup table from available tools.
• Check and save last value of AUTO_INCREMENT table column. For ex. its 3888763.
• ALTER AUTO_INCREMENT table column.

ALTER TABLE employee AUTO_INCREMENT = 3888763;

All suggestions are most welcome.