Auto-increment feature assigned to a column generates a unique key to the row. The attribute gets incremented automatically for every new row added. There are cases where we need to reset the value of the auto-increment column and start the numbering from specific numbers say '1001', etc..., instead of 1.

Initially, the auto-increment column is created like this:

CREATE TABLE tblstudents (

   roll int(11) NOT NULL AUTO_INCREMENT,

   name varchar(45) DEFAULT NULL,

 PRIMARY KEY (id)

);

After creating the auto-increment, other values can be added as shown:

INSERT INTO tbltemp(name) VALUES("sneha"),("Rakesh"),("Rohit");

Note that the roll number starts from 1 and gets automatically incremented for each entry.

ALTER TABLE

When we need to reset the auto increment value, we can use the ALTER TABLE statement as shown below:

ALTER TABLE table_name AUTO_INCREMENT = 'value';

ALTER TABLE tblstudents AUTO_INCREMENT = 1001;

These statements change the value of the index from next the entry. However, for MyISAM engines, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. On the other hand, for InnoDB engines, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not going to change.

TRUNCATE TABLE

The auto increment value can be also reset by using TRUNCATE TABLE. However, such formed statement removes all the data from the table. It is equivalent to recreating the table and hence has to be used with caution; or better unless you want to reset the whole table avoid it! The syntax of TRUNCATE TABLE is presented here:

TRUNCATE TABLE tableName;