Add Auto-Increment ID to existing table?

88

I have a pre-existing table, containing 'fname', 'lname', 'email', 'password' and 'ip'. But now I want an auto-increment column. However, when I enter:

ALTER TABLE users
ADD id int NOT NULL AUTO_INCREMENT

I get the following:

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

Any advice?:)

This question is tagged with mysql sql definition auto-increment

~ Asked on 2013-02-07 14:20:39

The Best Answer is


135

Try this

ALTER TABLE `users` ADD `id` INT NOT NULL AUTO_INCREMENT;

for an existing primary key

~ Answered on 2013-02-07 14:31:00


77

If you don't care whether the auto-id is used as PRIMARY KEY, you can just do

ALTER TABLE `myTable` ADD COLUMN `id` INT AUTO_INCREMENT UNIQUE FIRST;

I just did this and it worked a treat.

~ Answered on 2013-06-27 15:00:20


Most Viewed Questions: