Adding an Auto Increment Column After The Fact…

Yep, I pulled a ‘whoops’ today. I made a table, filled it with data, then realized I forgot my standard ‘id’.  My primary key, auto incrementing column that makes me feel good about myself.

Once I noticed it was gone, it was easy to add it back. Right? Right? Well, not so fast.  You can add the new column at the beginning of the table.  But as soon as you go to set it to a Primary, Auto increment, MySQL is going to cry because of all the zero values.  The primary key’s can’t be the same so it blows up.

Before setting it to be the primary key, you need to auto-fill it.  You can do it programmatically with any script of your choice, but I find it easy to do it with MySQL…

SET @pants := 0;
UPDATE `ratings` SET id = (SELECT @pants := @pants + 1);

And that’s it.  Time for a beer.