When we create a table we can give a auto increment value to a primary key filed.
This field is auto incremented by one for newly inserted value.
To start auto increment from specified number AUTO_INCREMENT = value is used while creating table.
Example:
1 2 3 4 5 6 7 | CREATE TABLE students ( id int(5) not null AUTO_INCREMENT, firstname varchar(30), lastname varchar(30), class varchar(30), PRIMARY KEY (id) ) AUTO_INCREMENT = 1001; |
If you have already created a table and then want to add some initial auto increment value then we have to alter the table as below.
auto increment will start from 1 by default.
1 2 3 4 5 6 7 | CREATE TABLE employee ( id int(5) not null AUTO_INCREMENT, firstname varchar(30), lastname varchar(30), department varchar(30), PRIMARY KEY (id) ); |
To start auto increment from some specified value lets say 100 we use alter table
Syntax
1 | ALTER TABLE table_name AUTO_INCREMENT = start_no; |
Example
1 | ALTER TABLE employee AUTO_INCREMENT = 100; |