What is auto increment
Auto increment is an option which automatically generates the value of a column.
MySQL provides AUTO_INCREMENT option to mark a column as auto increment.
When a row is inserted in a table, then the column marked as AUTO_INCREMENT generates a value which is 1 more than the value of this column that was last inserted.
Usually, primary key column is marked as AUTO_INCREMENT.
By default, the starting value of the column which is marked as
AUTO_INCREMENT will be 1.This means that when the first row is inserted in a table, then the
AUTO_INCREMENT column will be 1.But it is possible to change the initial value of auto increment at the time of table creation and after table is created.
While table creation
Syntax for specifying the initial value of an auto increment column while creating the table is by writing the initial value after
AUTO_INCREMENT option as shown below.
CREATE TABLE STUDENT(
id INT NOT NULL AUTO_INCREMENT 1000,
name VARCHAR2(100),
PRIMARY KEY(id)
);
For the above table, the value of id will start from 1000.
Thus, when first record will be inserted into the above table, its id will be 1000. For second record, the value of id will be 1001 and so on.
It is also possible to change the value of auto increment column even after the table is created and contains some records using ALTER TABLE statement.
Syntax for this is shown below.
ALTER TABLE [Table Name] AUTO_INCREMENT = [value];
In this case, the value for auto increment column starts from the given value when a new record is inserted after AUTO_INCREMENT initial value is modified.
Thus, suppose a table contains 2 records with the value of auto increment column as 1 and 2. When the following query is executed
ALTER TABLE [Table name] AUTO_INCREMENT = 100;
then the first record that is inserted after this query will have the value of auto increment column as 100, next one will have 101 and so on.