mysql, alter column remove primary key and auto incremement

Question

Hi,

I'm changing my mysql db table from an id (auto) to a uid.

ALTER TABLE companies DROP PRIMARY KEY;
ALTER TABLE companies ADD PRIMARY KEY (`uuid`);

This is the error I get..

[SQL] ALTER TABLE companies DROP PRIMARY KEY;
[Err] 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

Which I understand, I need to change the id to a non-autoincrement because I drop it as the primary key.? What is the syntax to change a column to remove primary key and auto increment?

ALTER TABLE companies change id id ?????????? int(11)

Answer 1

Have you tried just doing this:

ALTER TABLE companies CHANGE id id int(11);

The change doesn't expect you to tell it what you want to remove, but how you'd like the column to be defined.

---

Having read your question again, I realized I didn't really answer it - sorry about that.

If you need to remove the auto-increment and the primary key from the id column in a single SQL statement, this should do:

ALTER TABLE companies DROP PRIMARY KEY, CHANGE id id int(11);

In fact, you should be able to do everything in a single ALTER TABLE query:

ALTER TABLE companies
DROP PRIMARY KEY,
CHANGE id id int(11),
ADD PRIMARY KEY (uuid);