mysql add auto increment and a additional key

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

The table alre开发者_JAVA技巧ady 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 ?

---

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).

---

MySQL doesn't support AUTO_INCREMENT columns that aren't the primary key. One option is to make the AUTO_INCREMENT column the primary key, and just have a unique constraint on the other 'key'.