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

mysql auto-increment
Bobo picture Bobo · Jan 28, 2011 · Viewed 50.3k times · Source

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

A.Baudouin picture A.Baudouin · Jan 28, 2011

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.

Related questions

How to reset AUTO_INCREMENT in MySQL?

How can I reset the AUTO_INCREMENT of a field? I want it to start counting from 1 again.

Read More
How to set initial value and auto increment in MySQL?

How do I set the initial value for an "id" column in a MySQL table that start from 1001? I want to do an insert "INSERT INTO users (name, email) VALUES ('{$name}', '{$email}')"; Without specifying the initial …

Read More
How to make MySQL table primary key auto increment with some prefix

I have table like this table id Varchar(45) NOT NULL AUTO_INCREMENT PRIMARY KEY, name CHAR(30) NOT NULL, I want to increment my id field like 'LHPL001','LHPL002','LHPL003'... etc. What should I have to do for …

Read More