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

最新推荐文章于 2025-09-12 10:53:30 发布

转载最新推荐文章于 2025-09-12 10:53:30 发布 · 1.2k 阅读

mysql 专栏收录该内容

10 篇文章

订阅专栏

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

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)