關於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:外鍵刪除後,查詢表發現外鍵依舊存在,再刪一次會報錯:原因及相關解決辦法