MySQL Auto Increment on an existing table

Hello!

I’m having a little problem and any help would be much appreciated.

I have a table that contains data that was imported, this table has an ID column which I now need to set as an Auto Increment column. When I attempt to Alter the table to set it to Auto Increment I get an error :

Operation failed: There was an error while applying the SQL script to the database.
Executing:
ALTER TABLE `AF`.`TABLE_NAME` 
CHANGE COLUMN `ID` `ID` INT(11) NOT NULL AUTO_INCREMENT ,
ADD PRIMARY KEY (`ID`);
;

ERROR 1062: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
SQL Statement:
ALTER TABLE `AFI`.`TABLE_NAME` 
CHANGE COLUMN `ID` `ID` INT(11) NOT NULL AUTO_INCREMENT ,
ADD PRIMARY KEY (`ID`)

I thought that changing the table’s auto_increment value to a value higher than what was being inserted would solve my problem and tried this before attempting to alter the ID Column to :

ALTER TABLE TABLE_NAME AUTO_INCREMENT = 96000

But it didn’t help. From what I’m reading in the Error message the Alter resets the auto_increment value.

Help? :slight_smile:

Hector, check your database, the error is telling you that you already have 2 records with ID = 1
You need to be sure that all values in ID are different.

I created a MySQL table with 3 records 2 with ID 1 and executed the command and get the same error. If I then change one of the 1 to 2 (having 2 records with ID 2) I get the same error but instead of …entry ‘1’ for key… it say ‘2’

The table was defined with ID as VarChar and executed your ALTER TABLE command.

If I set the 3 records as 1, 3 and 536 I don’t get any error and the Autoincrement value is reported as 537 (using Valentina).

Thanks for the help Alberto!

I ended up creating a new table and inserting the data into the new table where I have created an auto increment column, I inserted all the values EXCEPT the ID which was generated by the table when the records were inserted. This worked for me since the ID’s have no gaps created by previous records being deleted from the middle of the table.

I will look into what you posted since I need to do the same process with various different tables (The fun… I know! LOL).

Again thank you very much for your post

1 Like