Mysql adds auto increment and optional key

Question

I am trying to modify a table with the addition of a new column, setting it as auto-increment and using the key.

There is already one key in the table, and this 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 am also trying to wrap a key name, i.e.

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

But still no luck.

How can I do that?

Answer 1

Nathan pretty much answered the question.

You will find the name (s) of existing indexes (s) using the SHOW INDEX FROM mydb.mytableSQL command .

First you need to drop the existing index using DROP_INDEX existing_index ON mydb.mytable.

Then you modify the table and add the primary index with the code.

, , CREATE UNIQUE INDEX unique_index ON mydb.mytable (column).

Answer 2

MySQL AUTO_INCREMENT, . , AUTO_INCREMENT "".