[mysql] How to add AUTO_INCREMENT to an existing column?

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

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

Simply just add auto_increment Constraint In column or MODIFY COLUMN :-

 ALTER TABLE `emp` MODIFY COLUMN `id` INT NOT NULL UNIQUE AUTO_INCREMENT FIRST;

Or add a column first then change column as -

1. Alter TABLE `emp` ADD COLUMN `id`;

2. ALTER TABLE `emp` CHANGE COLUMN `id` `Emp_id` INT NOT NULL UNIQUE AUTO_INCREMENT FIRST;

if you have FK constraints and you don't want to remove the constraint from the table. use "index" instead of primary. then you will be able to alter it's type to auto increment

I managed to do this with the following code:

ALTER TABLE `table_name`
CHANGE COLUMN `colum_name` `colum_name` INT(11) NOT NULL AUTO_INCREMENT FIRST;

This is the only way I could make a column auto increment.

INT(11) shows that the maximum int length is 11, you can skip it if you want.

ALTER TABLE Table name ADD column datatype AUTO_INCREMENT,ADD primary key(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 had existing data in the first column and they were 0's. First I made the first column nullable. Then I set the data for the column to null. Then I set the column as an index. Then I made it a primary key with auto incrementing turned on. This is where I used another persons answer above:

ALTER TABLE `table_name` CHANGE COLUMN `colum_name` `colum_name` INT(11) NOT NULL AUTO_INCREMENT FIRST;

This Added numbers to all the rows of this table starting at one. If I ran the above code first it wasn't working because all the values were 0's. And making it an index was also required before making it auto incrementing. Next I made the column a primary key.

This worked for me in case you want to change the AUTO_INCREMENT-attribute for a not-empty-table:

1.)Exported the whole table as .sql file
2.)Deleted the table after export
2.)Did needed change in CREATE_TABLE command
3.)Executed the CREATE_TABLE and INSERT_INTO commands from the .sql-file
...et viola

Alter table table_name modify table_name.column_name data_type AUTO_INCREMENT;

eg:

Alter table avion modify avion.av int AUTO_INCREMENT;