mysql add auto increment and a additional key

Question

I am trying to alter a table with adding a new column setting it as auto increment and with a key.

The table already has one key and this one will be an addition. The error I get is the following.

error : Multiple primary key defined

My code is:

alter table user add column id int (11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

I have also tries wrapping the key name ie

alter table user add column id int (11) NOT NULL AUTO_INCREMENT PRIMARY (id) KEY FIRST;

But still no luck.

How can it be done ?

Answer 1

nathan pretty much answered the question.

You find the name(s) of the existing index(es) by using the SHOW INDEX FROM mydb.mytable SQL command.

You have to drop the existing index first, using DROP_INDEX existing_index ON mydb.mytable.

Then you alter the table and add the primary index with your code.

Finally, you create the other index as a unique index, using CREATE UNIQUE INDEX unique_index ON mydb.mytable (column).