关于MYSQL:外键删除后,查询表发现外键依旧存在,再删一次会报错:原因及相关解决办法
1.创建表test6:外键名为test3_fk
mysql> create table test6(
-> id int,
-> test_name varchar(32),
-> constraint test3_fk foreign key(test_name) references student(name)
-> );
Query OK, 0 rows affected (0.40 sec)
mysql> desc test6;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| test_name | varchar(32) | YES | MUL | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.06 sec)
2.删除外键test3_fk;
mysql> alter table test6 drop foreign key test3_fk;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
3.再次显示表test6 发现外键依旧存在mysql> desc test6;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| test_name | varchar(32) | YES | MUL | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.06 sec)
4再删一次,发现报错:
mysql> alter table test6 drop foreign key test3_fk;
ERROR 1091 (42000): Can't DROP 'test3_fk'; check that column/key exists
正确的解决办法:
1.show index发现有与外键名相同的索引存在
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardi
nality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+---------------+
| test6 | 1 | test3_fk | 1 | test_name | A |
0 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+------
-------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
2.删除掉索引:
mysql> alter table test6 drop index test3_fk;
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0
3.再次查表:发现外键不存在
mysql> desc test6;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| test_name | varchar(32) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
原因是:
显然这是MYSQL的一个BUG:
1.MYSQL在建外键后,会自动建一个同名的索引
2.而删除外键的时候,这个同名索引如果没被删,则MYSQL认为外键仍然存在---MYSQL会在show keys命令里继续显示外键,当你drop table时,MYSQL也会继续提示你Can't DROP 'test3_fk'; check that column/key exists"
3.自然,当你再次想删除在show keys里看见的外键的时候,会报1091错误,这确实如网上说的,外键名错误,因为实际上外键已经不存在了.但可惜的是无论从show keys还是其他表操作你都能看到外键仍然是存在的,这就误导了你的眼睛
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2018-8-16 14:26:06 |
赞(
操作
