mysql> show keys from CORE_FUNCTION;
+---------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| CORE_FUNCTION | 0 | PRIMARY | 1 | FUNCTION_ID | A | 0 | NULL | NULL | | BTREE | |
| CORE_FUNCTION | 1 | FKDB8410785D1C928B | 1 | PARENT | A | 0 | NULL | NULL | YES | BTREE | |
+---------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)
mysql> alter table CORE_FUNCTION drop FOREIGN KEY FKDB8410785D1C928B;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
show keys查看发现外键仍然存在
mysql> show keys from CORE_FUNCTION;
+---------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| CORE_FUNCTION | 0 | PRIMARY | 1 | FUNCTION_ID | A | 0 | NULL | NULL | | BTREE | |
| CORE_FUNCTION | 1 | FKDB8410785D1C928B | 1 | PARENT | A | 0 | NULL | NULL | YES | BTREE | |
+---------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)
再删一次!,发现就删不掉了.报1025错误
mysql> alter table CORE_FUNCTION drop foreign key FKDB8410785D1C928B;
ERROR 1025 (HY000): Error on rename of './machome/CORE_FUNCTION' to './machome/#sql2-2648-1' (errno: 152)
网上查出的原因:现在网上这种四处转载的风潮真是很讨厌,google上几十个链接,都是一个文章,来回转,
曾经这是MySQL的一个bug，但是现在只是MySQL的一个不友好的地方。
例子如下：
mysql> ALTER TABLE ruler.test2child DROP FOREIGN KEY test;
ERROR 1025 : Error on rename of './ruler/test2child' to './ruler/#sql2-298-8f' (errno:
152)
这里并不是不能删除外键，而是使用了错误的外键名称，但是MySQL报的错实在离谱。
正确的错误信息要如下查询才能知道：
mysql> show innodb status;
------------------------
LATEST FOREIGN KEY ERROR
------------------------
071222 20:48:26 Error in dropping of a foreign key constraint of table
"ruler"."test2child",
in SQL command
ALTER TABLE ruler.test2child DROP FOREIGN KEY test
Cannot find a constraint with the given id "test".
显然,你照着上面google出的原因是无法解决的
真实的原因及解决办法:show index 发现有和外键同名的索引存在
mysql> show index from CORE_FUNCTION;
+---------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| CORE_FUNCTION | 0 | PRIMARY | 1 | FUNCTION_ID | A | 0 | NULL | NULL | | BTREE | |
| CORE_FUNCTION | 1 | FKDB8410785D1C928B | 1 | PARENT | A | 0 | NULL | NULL | YES | BTREE | |
+---------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
2 rows in set (0.01 sec)
删除掉索引
mysql> alter table CORE_FUNCTION drop index FKDB8410785D1C928B;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
再show keys看,外键不见了
mysql> show keys from CORE_FUNCTION;
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| CORE_FUNCTION | 0 | PRIMARY | 1 | FUNCTION_ID | A | 0 | NULL | NULL | | BTREE | |
+---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
1 row in set (0.00 sec)
显然这是MYSQL的一个BUG:
1.MYSQL在建外键后,会自动建一个同名的索引
2.而删除外键的时候,这个同名索引如果没被删,则MYSQL认为外键仍然存在---MYSQL会在show keys命令里继续显示外键,当你drop table时,MYSQL也会继续提示你"a foreign key constraint fails"
3.自然,当你再次想删除在show keys里看见的外键的时候,会报1025错误,这确实如网上说的,外键名错误,因为实际上外键已经不存在了.但可惜的是无论从show keys还是其他表操作你都能看到外键仍然是存在的,这就误导了你的眼睛
最新评论