MySQL外键与索引均无法删除_关于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还是其他表操作你都能看到外键仍然是存在的,这就误导了你的眼睛