mysql - 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.