Rectangle 27 0

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

Note
Rectangle 27 0

mysql How to add AUTO_INCREMENT to an existing column?


CREATE TABLE backupTable LIKE originalTable; 
INSERT backupTable SELECT * FROM originalTable;
DROP TABLE backupTable;
INSERT originalTable (col1, col2, col3) 
SELECT col1, col2,col3
FROM backupTable;
TRUNCATE TABLE originalTable;
  • To add AUTO_INCREMENT and PRIMARY KEY ALTER TABLE originalTable ADD id INT PRIMARY KEY AUTO_INCREMENT;

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

If you have a large table...or you want to do things a little smarter replace step 1 with: CREATE TABLE backupTable LIKE originalTable; RENAME TABLE originalTable TO originalTable.old, backupTable TO originalTable; Skip step 2 or you will loose all your data.

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

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

More on the duplication of tables using CREATE LIKE:

Note
Rectangle 27 0

mysql How to add AUTO_INCREMENT to an existing column?


ALTER TABLE t1 MODIFY b INTEGER NOT NULL AUTO_INCREMENT;

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

If you already have values in the table, you'll probably get a duplicate key error for value of 1. To change the value of the AUTO_INCREMENT the table must be using the MyISAM engine. So go to the table properties, change the engine from say InnoDB to MyISAM, then change the AUTO_INCREMENT value to whatever should be next in your sequence. Once you apply those changes you can switch the engine back to whatever you where using.

Might also want to include the current column value as an offset, eg ALTER TABLE tbl AUTO_INCREMENT = 100;

Note