Say that you need to add a unique index to an existing catalog table, so you decide to add a new column with a consecutive number in it. You may make it an auto_increment column, but that would work only for new inserts. How would you populate the column for the exisiting rows?
If you’re using mySQL you may try this little recipe:
First let’s add the column that we’ll use later as primary key.
Now we declare a counter and use it to populate each row.
UPDATE mytable SET id=(@i:=@i+1);
Finally, we transform the new column into the primary key.
There you go. A new auto-increment primary key that has been correctly populated for all existing rows.