Troubles of adding a new ID (auto increment) after table exist

Question

I have a table with 38.000 records, but without any auto increment column like ID.
Now I have to add an ID column, and I'm wondering could there be troubles?

Answer 1

You can add do that without problem only if your table doesn't have relationship with others.

You must remove the old primary key and upload the table accordingly (perhaps add an unique index on the old primary key).

Proceed like that :

• Make a dump of your database

• Remove the primary key like that

ALTER TABLE XXX DROP PRIMARY KEY

• Add the new column like that

ALTER TABLE XXX add column Id INT NOT NULL AUTO_INCREMENT FIRST, ADD primary KEY Id(Id)

The table will be looked and the AutoInc updated.

Answer 2

This will add an auto increment ID to your MySQL table:

ALTER TABLE  `your_table_name` ADD  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Depending on the amount of data on your table it could take a few minutes to complete.

Answer 3

This is the solution that i tried with MySQL Workbench:

ALTER TABLE `tableName` CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT  ;

I don't know if this is correct way, but I didn't notice any problem in my Java EE Application yet.

Answer 4

I work with very large data and had an id column filled with NULLS. I chose to run the following SQL to fill with numbers... then I set the id column to be my primary key.

set @row = 0;
UPDATE philadelphia.msg_geo_sal SET id = @row := @row + 1;