Altering table to auto_increment?

[coldwerturkey]

I used

CREATE table FROM SELECT * FROM anothertabler

to duplicate and rename a bunch of different tables, and for whatever reason it carried everything but the primary key's and auto_increment attributes of each tables 'id' field..

I tried

ALTER TABLE table CHANGE id id NOT NULL PRIMARY KEY AUTO_INCREMENT

but I got an syntax error...

Can anyone shed some light on why CREATE FROM doesn't exactly duplicate and what syntax I need to fix it?

[devinemke]

i just did a test on my dev box...

CREATE TABLE new_table SELECT * FROM old_table

...and everything worked as expected, including the proper auto_increment primary key field.

i'm using mySQL 5.1.30

[coldwerturkey]

I guess this is a WTF moment then?

edit:

I just started from scratch; couldn't figure out how to make a non-auto_increment colunm auto-increment field..

for reference:

ALTER TABLE table DROP id;
ALTER TABLE table ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
ADD PRIMARY KEY (id);