MySQL: Forgot to set Auto Increment

Question

I set up a database, with an auto incrementing column that was meant to be an ID. The problem is that I forgot to check off auto increment when creating the table in phpMyAdmin...and didn't notice until now! So what I have is something like:

ID   |   column one   |   column two
------------------------------------
0    |   some value   |   some value
0    |   other value  |   something else
0    |   example val. |   something
0    |   my example   |   something

Basically, my "auto incrementing" column shows zeros across the board. How could I modify this table to show auto increment for all previous and all future rows?

Answer 1

If the table were empty you could just do:

ALTER TABLE mytable MODIFY COLUMN id bigint not null primary key auto_increment

but I'm not sure what will happen with rows in it. It might be best to create a new one with the correct definitions, copy the data over minus the id column, drop the old (wrong) table, and move the new one to the old name.

-- Either: 
CREATE TABLE newtable LIKE mytable;
ALTER TABLE newtable MODIFY COLUMN id bigint not null primary key auto_increment;
-- or:
CREATE TABLE newtable (
    id bigint not null primary key auto_increment,
    column1 type1,
    column2 type2,
    ...
);

INSERT INTO newtable 
    (column1, column2, ...) 
    SELECT column1, column2, column3, ... 
    FROM mytable;
-- Make SURE that insert worked BEFORE you drop the old table 
-- and lose data if it didn't.
DROP TABLE mytable;
ALTER TABLE newtable RENAME TO mytable;

I'm sure phpMyAdmin has the ability to do this more graphically.

Answer 2

First update your records (otherwise you will get duplicate primary key):

UPDATE `tablename` SET id = 1 WHERE "column one" = 'some value';
UPDATE `tablename` SET id = 2 WHERE "column one" = 'other value';
UPDATE `tablename` SET id = 3 WHERE "column one" = 'example val.';
UPDATE `tablename` SET id = 4 WHERE "column one" = 'my example';

Then add the primary key/auto_increment:

ALTER TABLE tablename MODIFY COLUMN id int(11) NOT NULL primary key auto_increment;

Then set the next auto_increment:

ALTER TABLE tablename  AUTO_INCREMENT = 5;

Answer 3

You could drop the ID column and re-create it. The ids will be reassigned, if you remembered to enable the auto increment ;)