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?

You have a schema of the table? – Book Of Zeus Oct 30 '11 at 22:24 — Book Of Zeus, Oct 30 '11 at 22:24

Kevin · Accepted Answer · 2011-10-30T22:34:28.713

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.

Yes, this will work. Just modify the column to be a `PRIMARY KEY` and `auto_increment`. — ypercubeᵀᴹ, Oct 30 '11 at 22:34

score 7 · Answer 2 · answered Oct 30 '11 at 22:27

7

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;

answered Oct 30 '11 at 22:27

Book Of Zeus

49,509
18
174
171

Updating all the records is going to be awfully slow and labor-intensive if there's more than a couple lines. – Kevin Oct 30 '11 at 22:40
I agree, but in the example there's 4 records. I up voted yours if the user has lots of records – Book Of Zeus Oct 30 '11 at 22:42

score 4 · Answer 3 · answered Oct 30 '11 at 22:27

4

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

answered Oct 30 '11 at 22:27

Marcus

12,296
5
48
66

MySQL: Forgot to set Auto Increment

3 Answers3