How to add AUTO_INCREMENT to an existing column?

Question

How do I add auto_increment to an existing column of a MySQL table?

Answer 1

I think you want to MODIFY the column as described for the ALTER TABLE command. It might be something like this:

ALTER TABLE users MODIFY id INTEGER NOT NULL AUTO_INCREMENT; 

Before running above ensure that id column has a Primary index.

Answer 2

Method to add AUTO_INCREMENT to a table with data while avoiding “Duplicate entry” error:

1. Make a copy of the table with the data using INSERT SELECT:

   CREATE TABLE backupTable LIKE originalTable;  INSERT backupTable SELECT * FROM originalTable; 

2. Delete data from originalTable (to remove duplicate entries):

   TRUNCATE TABLE originalTable; 

3. To add AUTO_INCREMENT and PRIMARY KEY

   ALTER TABLE originalTable ADD id INT PRIMARY KEY AUTO_INCREMENT; 

4. Copy data back to originalTable (do not include the newly created column (id), since it will be automatically populated)

   INSERT originalTable (col1, col2, col3)  SELECT col1, col2,col3 FROM backupTable; 

5. Delete backupTable:

   DROP TABLE backupTable; 

I hope this is useful!

More on the duplication of tables using CREATE LIKE:

Duplicating a MySQL table, indexes and data