0

When I'm trying to enable auto_increment on a column in phpmyadmin, but I always get this error:

#1062 - Duplicate entry '1' for key 1

The column in question is the PK and an INT. It already has several rows in it. Because of this, I tried the following:

ALTER TABLE Persons AUTO_INCREMENT=7

This should have set the auto incrementing to start at 7, and so skipping the 0 to 6 that already exist in that row... but it didn't work. Or that was the theory.

What am I doing wrong?

Update:

I have tried removing the PK index on the column and running a query setting the column to NULL. Everything was set to NULL. I tried to then add auto increment and I got the following error: #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key. I try to add a PK index again and get the following #1062 - Duplicate entry '0' for key 1. Argh.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
Chuck Le Butt
  • 47,570
  • 62
  • 203
  • 289

2 Answers2

1

Try this: alter table Persons modify id integer unsigned primary key auto_increment;

were id is the column of interest

Vivian
  • 11
  • 1
0

When trying to add an auto_increment to an existing table, you need to make sure all of the key values already in the table are all unique.

Setting the column value to NULL should work. mySQL will then auto-fill the values with auto increment ones.

Pekka
  • 442,112
  • 142
  • 972
  • 1,088
  • So go through that column and replace it with `NULL`s and try again? – Chuck Le Butt Feb 20 '12 at 11:41
  • @Django yeah, or just `UPDATE table SET columnname = NULL` – Pekka Feb 20 '12 at 11:45
  • @Django Yes, update table column with null (be sure to remove key constraints), then create autoincrement primary key.Mysql will do rest for you. – rkosegi Feb 20 '12 at 11:47
  • @Pekka Ugh :( Thanks for the help, but I get the same message when I run that query: `#1062 - Duplicate entry '0' for key 1 ` – Chuck Le Butt Feb 20 '12 at 11:48
  • @Django does your column have a default value? – Pekka Feb 20 '12 at 11:49
  • @Pekka No, there's no default value. – Chuck Le Butt Feb 20 '12 at 11:51
  • @Django ahh, maybe it has to be nullable, too? I can't remember right now and I can't try out. Can you try making the column `NULL` and run it again? – Pekka Feb 20 '12 at 11:53
  • @Pekka Ok, I removed the PK index and ran the query, switched it to nullable. Now everything is set to `NULL`. I try to then add auto increment and I get the following error: `#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key`. I try to add a PK index again and get the following `#1062 - Duplicate entry '0' for key 1`. Argh. – Chuck Le Butt Feb 20 '12 at 11:56
  • @Django ugh! Does it have to be a primary key though? Can you try just creating a normal index and then setting the auto increment? – Pekka Feb 20 '12 at 12:02
  • @Django creating a normal index and then imposing the `auto_increment` seems to work for me. – Pekka Feb 20 '12 at 12:03
  • @Pekka Hurrah! Setting it to an normal index worked! (I've since switched it to a PK -- and it still works.) What a nightmare. Thanks very much for your help! – Chuck Le Butt Feb 20 '12 at 12:06