Wed Mar  3 08:13:22 CST 2004

So, you're getting an error like the following?

ERROR 1062: Duplicate entry '1' for key 1

I got it because I was trying to alter a table to set an ID field into an auto_increment-ing primary key.
The reason I was getting it is because the table contained a row with an ID of 0, which is a special
value for auto_increment. if you try inserting a 0 or NULL into an auto_incrementing field it will
assign the row the next available value.

The workaround is to either permanently change that id from 0 to something else (which may or may not
involve modifying data in other tables and may or may not be possible because of historical data reasons)
***OR*** update the ID to something other than zero, alter the table and then update that record back to 0.

It's a kludge, but it works.

Here is what I was playing with:



mysql> create table auto_inc_alter (id int not null default 0, name char not null) PACK_KEYS=1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into auto_inc_alter values (0,"A"),(1,"B"),(2,"C"),(4,"D"),(5,"E");
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from auto_inc_alter;
+----+------+
| id | name |
+----+------+
|  0 | A    |
|  1 | B    |
|  2 | C    |
|  4 | D    |
|  5 | E    |
+----+------+
5 rows in set (0.00 sec)

mysql> alter table auto_inc_alter modify column id int unsigned not null auto_increment primary key;
ERROR 1062: Duplicate entry '1' for key 1
mysql> alter table auto_inc_alter modify column id int unsigned not null auto_increment primary key, AUTO_INCREMENT=0;
ERROR 1062: Duplicate entry '1' for key 1
mysql> update auto_inc_alter SET id = 6 WHERE id = 0;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> alter table auto_inc_alter modify column id int unsigned not null auto_increment primary key;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> update auto_inc_alter SET id = 0 WHERE id = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from auto_inc_alter;
+----+------+
| id | name |
+----+------+
|  0 | A    |
|  1 | B    |
|  2 | C    |
|  4 | D    |
|  5 | E    |
+----+------+
5 rows in set (0.00 sec)

mysql> insert into auto_inc_alter values (NULL,"F");
Query OK, 1 row affected (0.00 sec)

mysql> select * from auto_inc_alter;
+----+------+
| id | name |
+----+------+
|  0 | A    |
|  1 | B    |
|  2 | C    |
|  4 | D    |
|  5 | E    |
|  7 | F    |
+----+------+
6 rows in set (0.00 sec)

mysql>
mysql> insert into auto_inc_alter values (0,"G");
Query OK, 1 row affected (0.00 sec)

mysql> select * from auto_inc_alter;
+----+------+
| id | name |
+----+------+
|  0 | A    |
|  1 | B    |
|  2 | C    |
|  4 | D    |
|  5 | E    |
|  7 | F    |
|  8 | G    |
+----+------+
7 rows in set (0.00 sec)

mysql> delete from auto_inc_alter where id = 0;
Query OK, 1 row affected (0.00 sec)

mysql> insert into auto_inc_alter values (0,"H");
Query OK, 1 row affected (0.00 sec)

mysql> select * from auto_inc_alter;
+----+------+
| id | name |
+----+------+
|  9 | H    |
|  1 | B    |
|  2 | C    |
|  4 | D    |
|  5 | E    |
|  7 | F    |
|  8 | G    |
+----+------+
7 rows in set (0.00 sec)

mysql>