mysql - adding primary key to a large table

Question

Have a large table here with no primary key and im trying to add a column called "id" at the beginning of the table. Im using phpMyAdmin but cant figure it out. Its easy enuff to add a column, but how do u set up the auto-increment feature to automatically assign a key to each row? Any suggestions???

Answer 1

Use:

ALTER TABLE `tablename` ADD `FIELDNAME` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST

In phpmyadmin (depending on your version) set the checkbox on AI (auto_increment) and the index on primary. The result is a SQL as postet above.

Answer 2

Nice trick: first rename the table, then create a new one with the ID column (and auto_increment). Then make a dump of the original table, and tweak the query a bit to enter all the rows back into the original one. The auto-increment will assign ID's automatically. Dirty, but effective.

Answer 3

when you add coloumn in phpmyadmin it has a chechBox (AUTO_INCREMENT) if you checked it you can create field with auto-increment feature.