An auto-incremented column can become a little cluttered if records are deleted from the table, and you may wish to delete the slack of the sequence in your id column for instance doesn't bear any relationship to the number of records in the table.
Before doing anything about that to a primary key, bear in mind that there are those who say that a primary key should never, ever be changed. Do you really need to re-number your primary key?
If you really want to do it, read the caveat below before proceeding.
CAVEAT: I haven't actually tested this!
First, be safe and backup your database.
Next copy your table:
CREATE TABLE mytable2 SELECT * FROM mytable1 |
Now run the following commands, modified as necessary for your situation, on the copy:
ALTER TABLE mytable2 DROP mycolumn;
ALTER TABLE mytable2 ADD mycolumn int(6) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
Have a look at the new table. Browse the records. Do you like what you see? Run as many tests as are appropriate to your situation. More is better than less.
If you're satisfied, it's time to disable mytable1 and make mytable two current.
Rename mytable1 to mytable1_old, then rename mytable2 to mytable1.
Keep a close an eye on the situation. If you're not happy, you can revert to mytable1_old or even restore the backup.