How to add AUTO_INCREMENT to an existing column?

Alter table table_name modify column_name datatype(length) AUTO_INCREMENT PRIMARY KEY

You should add primary key to auto increment, otherwise you got error in mysql.


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.


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;
    

More on the duplication of tables using CREATE LIKE:

Duplicating a MySQL table, indices, and data