mysql无法删除表中的主键
1、MySQL删除表中的主键时报错,如下代码所示:
创新互联专注于企业全网整合营销推广、网站重做改版、霸州网站定制设计、自适应品牌网站建设、H5开发、商城系统网站开发、集团公司官网建设、成都外贸网站建设公司、高端网站制作、响应式网页设计等建站业务,价格优惠性价比高,为霸州等各大城市提供网站开发制作服务。
mysql> alter table student drop primary key; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
2、问题原因:
查看student表的类型时,发现主键列中有auto_increment(递增)类型选项。如要删除表中的主键,需要先删除auto_increment类型。代码如下所示:
mysql> desc student; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | char(20) | NO | MUL | NULL | | | age | tinyint(2) | NO | MUL | 0 | | | dept | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ 4 rows in set (0.02 sec)
3、删除student表中的主键列上面的auto_increment类型。代码如下所示:
mysql> alter table student change id id int; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0
提示:alter table student change id id int;命令修改student表中列的类型,auto_increment类型自然就会被删除。
4、查看student表的类型,发现auto_increment类型已被删除。代码如下所示:
mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | name | char(20) | NO | MUL | NULL | | | age | tinyint(2) | NO | MUL | 0 | | | dept | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)
5、删除student中的主键。代码如下所示:
mysql> alter table student drop primary key; Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0
6、最后查看student表的类型,发现表中的主键已被删除。代码如下:
mysql> desc student; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | name | char(20) | NO | MUL | NULL | | | age | tinyint(2) | NO | MUL | 0 | | | dept | varchar(16) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 4 rows in set (0.00 sec)
网站栏目:mysql无法删除表中的主键
标题链接:http://scyanting.com/article/pijgis.html