mysql无法删除包含外键的索引

时间:2014-01-14 07:01:20      作者:beebol      标签: drop index foreign key      分类: Mysql

今天在做实验时，发现个问题，创建了个外键，然后通过drop index  userid  on area,报错，具体的如下：

[root@localhost(student) Tue Jan 14 18:47:00 2014]>show create table users;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                       |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `userid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(12) DEFAULT NULL,
  `password` varchar(64) DEFAULT NULL,
  `score` float DEFAULT '0',
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[root@localhost(student) Tue Jan 14 19:31:37 2014]>show create table area;
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                |
+-------+-----------------------------------------------------------------------------------------------------------------------------+
| area  | CREATE TABLE `area` (
  `userid` int(11) NOT NULL,
  `area` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)
1 row in set (0.00 sec)

前面是users表和area表结构，area表中的userid列是外键。

如下是两个表的数据：

[root@localhost(student) Tue Jan 14 18:17:13 2014]>select * from users where userid=3;
+--------+----------+---------------+-------+
| userid | username | password      | score |
+--------+----------+---------------+-------+
|      3 | aaaa     | aaaaaaaaaaaaa |    30 |
+--------+----------+---------------+-------+
1 row in set (0.00 sec)

[root@localhost(student) Tue Jan 14 18:16:37 2014]>select * from area;
+--------+---------+
| userid | area    |
+--------+---------+
|      4 | beijin  |
|      2 | wuhan   |
|      1 | jiangsu |
|      3 | henan   |
+--------+---------+4 rows in set (0.01 sec)
4 rows in set (0.01 sec)

然后，我执行如下语句时报错：

[root@localhost(student) Tue Jan 14 18:18:25 2014]>show index from area;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| area  |          1 | userid   |            1 | userid      | A         |           4 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
[root@localhost(student) Tue Jan 14 18:18:36 2014]>drop index userid on area;ERROR 1025 (HY000): Error on rename of './student/#sql-699_d' to './student/area' (errno: 150)
ERROR 1025 (HY000): Error on rename of './student/#sql-699_d' to './student/area' (errno: 150)

当时还以为drop index 语法写错了，重新看了下drop index 帮助档是这样的。到网上查了下，官网说这是个bug。详细可以看：Bug #21395   。后面 这个bug在innodb 1.0.2版本已经修复了。在删除有外键的复合索引时，需要先将外键删除，才能删除索引。修复后的执行就会有明确的提示，叫你先删除外键。

alter table foo drop index fooIdx;ERROR 1553 (HY000): Cannot drop index 'fooIdx': needed in a foreign key constraint
ERROR 1553 (HY000): Cannot drop index 'fooIdx': needed in a foreign key constraint

目前我的版本下也是一样的，必须先删除外键，然后再删除索引。我的innodb的版本是1.0.13

[root@localhost(student) Tue Jan 14 18:46:35 2014]>alter table area drop foreign key area_ibfk_1;
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

[root@localhost(student) Tue Jan 14 18:46:46 2014]>show create table area;                       
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| area  | CREATE TABLE `area` (
  `userid` int(11) NOT NULL,
  `area` varchar(20) DEFAULT NULL,
  KEY `userid` (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[root@localhost(student) Tue Jan 14 18:46:48 2014]>alter table area drop index userid;      
Query OK, 4 rows affected (0.08 sec)Records: 4  Duplicates: 0  Warnings: 0
Records: 4  Duplicates: 0  Warnings: 0

解决问题了，想想还是在学习mysql 过程中遇到的问题记录下来。