Insert auto increment primary key to existing table
An ALTER TABLE statement adding the PRIMARY KEY column works correctly in my testing:
ALTER TABLE tbl ADD id INT PRIMARY KEY AUTO_INCREMENT;On a temporary table created for testing purposes, the above statement created the AUTO_INCREMENT id column and inserted auto-increment values for each existing row in the table, starting with 1.
suppose you don't have column for auto increment like id, no, then you can add using following query:
ALTER TABLE table_name ADD id int NOT NULL AUTO_INCREMENT primary key FIRSTIf you've column, then alter to auto increment using following query:
ALTER TABLE table_name MODIFY column_name datatype(length) AUTO_INCREMENT PRIMARY KEY
For those like myself getting a Multiple primary key defined error try:
ALTER TABLE `myTable` ADD COLUMN `id` INT AUTO_INCREMENT UNIQUE FIRST NOT NULL;On MySQL v5.5.31 this set the id column as the primary key for me and populated each row with an incrementing value.