Set auto_increment on existing table (MySQL)

Set Auto Increment in existing table where the table have data

mysql> select * from employee;
+---------------------+-----------------------+----------------------------------+-------------+
|                  ID | FULLNAME              | EMAIL                            | PHONENUMBER
|
+---------------------+-----------------------+----------------------------------+-------------+
|                   1 | James Bond            | james.bond@xyz.com               | 46700000007 |
|                   2 | Mediline              | mediline@xyz.com                 | 46724312313 |
+---------------------+-----------------------+----------------------------------+-------------+

In this example case we need to set auto_increment value 3

ALTER TABLE employee AUTO_INCREMENT = 3; 

ALTER TABLE employee MODIFY ID int AUTO_INCREMENT;


If you have value 0 on the column where you want to set auto_increment then you will get the following error:

ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'


mysql> select * from employee;
+---------------------+-----------------------+----------------------------------+-------------+
|                  ID | FULLNAME              | EMAIL                            | PHONENUMBER
|
+---------------------+-----------------------+----------------------------------+-------------+
|                   0 | Test                  | testtes@xyz.com                  | 123456      |
|                   1 | James Bond            | cemilbedran.yildirim@xyz.com     | 46700000007 |
|                   2 | Mediline              | mediline@xyz.com                 | 46724312313 |
+---------------------+-----------------------+----------------------------------+-------------+

ALTER TABLE employee AUTO_INCREMENT = 3; 

ALTER TABLE employee MODIFY ID int AUTO_INCREMENT;

ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'

Cause: It is not possible to set auto_increment on a column where value started with 0 because auto_increment starts from 1.

Solution:  Delete the rows where ID is 0 or update value ID.


You can see the next auto-increment value using the following query:

SELECT AUTO_INCREMENT                                                                             
FROM information_schema.tables
WHERE table_name = <Table Name>;

Comments