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 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)
好像是这样的,自增字段必须是主键。删除主键但对应字段是自增的话,抛错。