MySQL populate new column with incrementing values without auto_increment

Question

I see lots of almost similar questions with obvious answers but I'm fairly sure this question isn't already on here.

I need to add an auto-incrementing id column to an existing table and set it as the primary key. I can't lose any of the existing data.

I can successfully make the change to the table structure but I get an error about truncated data in the new column. When I view the data every value in the new auto-incrementing column is null (and therefore not unique).

How can I back-fill these values to ensure uniqueness in my primary key?

***I would prefer to avoid dumping the existing data to a temporary table and re-inserting if there is a simpler solution.

Current script:

alter table the_table add new_field int first;
alter table the_table drop primary key, add primary key (new_field);
alter table the_table change new_field new_field int unsigned not null auto_increment;

I run the script in this order as I can't have an auto-incrementing column that isn't the primary key.

(MySQL 5.3)

Answer 1

Try creating the column, setting it as primary key and auto increment in one go

ALTER TABLE `the_table` ADD `new_field` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;