mysql - 如果一个主键有auto_increment，删除该主键之前为什么需要先去掉auto_increment

问题描述

mysql> SHOW COLUMNS FROM tb;+----------+----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+----------+----------------------+------+-----+---------+----------------+| id | smallint(6) | NO | PRI | NULL | auto_increment || 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;ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a keymysql> ALTER TABLE tb MODIFY id SMALLINT NOT NULL;Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> 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: 0mysql> 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)

问题解答

好像是这样的，自增字段必须是主键。删除主键但对应字段是自增的话，抛错。