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:
Make a copy of the table with the data using INSERT SELECT:
CREATE TABLE backupTable LIKE originalTable; INSERT backupTable SELECT * FROM originalTable;
Delete data from originalTable (to remove duplicate entries):
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)
INSERT originalTable (col1, col2, col3) SELECT col1, col2,col3 FROM backupTable;
Delete backupTable:
DROP TABLE backupTable;
More on the duplication of tables using CREATE LIKE:
Duplicating a MySQL table, indices, and data