如果一个主键有auto_increment，删除该主键之前必须要先删除掉auto

本文记录了MySQL中表tb的结构调整过程，包括字段类型修改、主键删除等操作，并展示了每一步操作后的表结构详情。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql> ALTER TABLE tb MODIFY id SMALLINT NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM tb;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(6) | NO | PRI | NULL | |
| username | varchar(20) | NO | UNI | NULL | |
| age | smallint(6) | NO | | NULL | |
| score | smallint(5) unsigned | YES | | 0 | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE tb DROP PRIMARY KEY;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW COLUMNS FROM tb;
+----------+----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------+------+-----+---------+-------+
| id | smallint(6) | NO | | NULL | |
| username | varchar(20) | NO | PRI | NULL | |
| age | smallint(6) | NO | | NULL | |
| score | smallint(5) unsigned | YES | | 0 | |
+----------+----------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)