How to remove primary key with auto increment?

Question

MySQL

Hello!
I'm trying to remove the PRIMARY KEY attribute from the table below!

+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| doughnut_name | varchar(10) | YES  |     | NULL    |                |
| doughnut_type | varchar(6)  | YES  |     | NULL    |                |
| id            | int         | NO   | PRI | NULL    | auto_increment |
+---------------+-------------+------+-----+---------+----------------+

team

ALTER TABLE `doughnut_list`
DROP PRIMARY KEY ;

An error is thrown: "ERROR: 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a
key " it should be declared as a key "(well, from this clumsily translated phrase, I only understand that it swears at autoincrement)
I BEFORE created a new property, idbut already without AUTO_INCREMENTbut withPRIMARY KEY

+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| doughnut_name | varchar(10) | YES  |     | NULL    |       |
| doughnut_type | varchar(6)  | YES  |     | NULL    |       |
| id            | int         | NO   | PRI | NULL    |       |
+---------------+-------------+------+-----+---------+-------+

and executing the command accordingly we get the expected result:ALTER TABLE `doughnut_list` DROP PRIMARY KEY;

+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| doughnut_name | varchar(10) | YES  |     | NULL    |       |
| doughnut_type | varchar(6)  | YES  |     | NULL    |       |
| id            | int         | NO   |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+

But I'm confused, if the column with the name iditself is not deleted, then why does the interpreter swear at AUTO_INCREMENT? Well, this one would have been removed PRIMARY KEY, but the column would have remained, vmylse id? I don't understand this mechanics. it turns out that in order to delete a column that is PRIMARY KEYand which has it, it is also AUTO_INCREMENTnecessary to delete it completely, and recreate it? Or is there still a way to do it in one command without recreating the column? And in general, what is the connection PRIMARY KEYin AUTO_INCREMENTthis case? Why does this error occur, that they say it is impossible to drop primer ki while hanging on this column AUTO_INCREMENT? The column is not actually deleted, well, let it be with this AUTO_INCREMENTvolume ...

Answer 1

If a field is declared as AUTO_INCREMENT, then it MUST be a primary key or its prefix. If you remove the PRIMARY KEY but NOT remove the AUTO_INCREMENT, the resulting structure will not meet this requirement - hence the error.
Therefore, in one ALTER TABLE, you should perform both operations - and remove the primary key, and the AUTO_INCREMENT attribute (for example, change to DEFAULT {value}). Or execute two ALTER TABLEs, but in reverse order - first remove the AUTO_INCREMENT attribute, and only then the primary key.
DEMO