mysql add auto increment and a additional key

Question

Hey all

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

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

Answer 2

nathan pretty much answered the question.

You have to drop the existing index first, using DROP_INDEX.

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.