Alter Table

[Tiger66]

HI I am trying to change one of my database field to auto_increment.
I am getting an "invalid alter table option" when I tried to alter my table with code below

alter table PC modify ID NUMBER(10) AUTO_INCREMENT NOT NULL

Please help

Thanks

[drawmack]

1) the field must be of type int, tinyint, smallint or bigint
2) the field cannot contain any data that breaks the auto_increment rules
3) the field must be tagged as not null
4) the field must be an index
5) there can only be one auto_increment field per table

I recommend creating a new field that is auto_increment, then drop the old field and rename the new field to the old field's name. If you have any foreign keys that point to this field you will have to make sure they are updated after you add the new field and before you drop the old field.

[Tiger66]

Hi Drawmack

I tried following your role by changing my code to
ALTER TABLE PC ADD ID INT NOT NULL AUTO_INCREMENT, ADD INDEX (ID)

I am still getting the same error
Any idea?

Thanks

[drawmack]

what if you create a new table with the auto_increment field and then move the data from the old table to the new one?