Add auto increment to existing column

[Foamcow]

Hi,

This is probably dead simple but I'm stuck!

I have a table with 126 rows in it. The guy that originally created the table didn't think to make the id column an auto incremented one.

For the PHP script I am writing I could really do with making the id column auto increment.

I've tried

ALTER TABLE products CHANGE product_id product_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT

But that throws a duplicate entry error.
Basically I want to convert product_id column to auto increment starting from 127 and keep the first 126 in there with the id numbers they already have.



<honk>*:O)</honk>
Foamcow Heavy Industries - Web site design in Cheltenham and Gloucester
Ham and Jam - British & Commonwealth forces mod for Half Life 2

 

[Foamcow]

I should add that I am working with MySQL 4.0.18

<honk>*:O)</honk>
Foamcow Heavy Industries - Web site design in Cheltenham and Gloucester
Ham and Jam - British & Commonwealth forces mod for Half Life 2

 

[Foamcow]

It appears there is bug.

There was a row with the id as '0'.
I removed that and it let me use

ALTER TABLE products CHANGE product_id product_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT

<honk>*:O)</honk>
Foamcow Heavy Industries - Web site design in Cheltenham and Gloucester
Ham and Jam - British & Commonwealth forces mod for Half Life 2

 

[guelphdad]

It isn't a bug. you can't have a value of less than 1 in an auto increment column.

 

[Foamcow]

Yeah, that makes sense. Thanks.

When I researched it I found a bug report on the MySQL site.
It refered to values of 0 and 1, I removed the 2 rows that had those id's and it then let me make the column an auto increment one.
I've not tested beyond that so it may or may not be true!




<honk>*:O)</honk>
Foamcow Heavy Industries - Web site design in Cheltenham and Gloucester
Ham and Jam - British & Commonwealth forces mod for Half Life 2