Notas de la Etiqueta: auto_increment

MySQL: Setear Auto Ingrement en una tabla con datos ó Set Auto Increment in existing table where the table have data

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