mysqlprimary-key

automatically insert auto increment primary key and values into existing table

I am trying to alter a table which has no primary key nor auto_increment column. I know how to add an auto increment primary key column.

I already have 500 rows in the DB and want to give them ids but I don't want to do it manually.

How do I insert data into the primary key column automatically?

Solution

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