MySQL auto increment

MySQL auto_increment

auto_increment is keyword whose values are generated by system itself . every time a new record is entered in the database , the value of this field is incremented by one. for example: consider a employee table tat contain four fields name, emp_id, email, mobile number in this table emp_id is defined as auto_increment. for any new user only three fields need to provide value , the fourth field is auto generated. This field must be defined as integer.

How to rearrange the auto_increment field(Emp_id) after being deleted row 4

Query rearrange the auto_increment value

	Alter table emp drop Emp_id;

	Alter table emp auto_increment=1;

	Alter table emp ADD Emp_id bigint unsigned not null  auto_increment primary key first;

In above example suppose we deleted entry 4 from the table , then we have to rearrange the records in the table and change the value of auto_incremented variable accordingly.

Initialize auto_increment field(Emp_id) from 1000 at the place of 1.

	Alter table emp drop Emp_id;

	Alter table emp auto_increment=1000;

	Alter table emp ADD Emp_id bigint unsigned not null
  auto_increment primary key first;

The value of auto_increment is 1 by default. if we want to start the value from any different values say 1000 then we can do it with the help of above mentioned query.