Add auto_increment PK to table

from the Artful MySQL Tips List

Given a table named tbl to which you need to add an auto_incrementing primary key and populate the key values ...
drop table if exists tblbak, tblnew;
create table tblbak select * from tbl;
create table tblnew like tbl;
alter table tblnew add column id int unsigned primary key auto_increment first;
insert into tblnew(col1,col2,...) select * from tbl;
drop table tbl;
rename table tblnew to tbl;  

Last updated 19 Dec 2012

Return to the Artful MySQL Tips page