MySQL: Forgot to install Auto Increment

Question

I created a database with an auto-increment column, which was supposed to be an identifier. The problem is that I forgot to disable auto-increment when creating a table in phpMyAdmin ... and have not noticed so far! So, I have 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 all over the board. How can I modify this table to show auto-increment for all previous and all future rows?

Answer 1

If the table was empty, you could simply do:

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

but I'm not sure what will happen to the lines in it. It’s best to create a new one with the correct definitions, copy the data minus the id column, delete the old (wrong) table and transfer 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 am sure phpMyAdmin has the ability to do this more graphically.

Answer 2

First update your records (otherwise you will get a 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 following auto_increment:

 ALTER TABLE tablename AUTO_INCREMENT = 5; 

Answer 3

You can delete the identifier column and recreate it. IDs will be reassigned if you remember to enable auto-increase;)