Nota: yo tuve que eliminar el registro con id = 0 (o setearle un valor distinto) a través de phpMyAdmin, por consola me tiraba otro error.
Fuente: http://www.nazmulhuda.info/set-auto_crement-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>;