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>;