About MySQL: After the foreign key delete, the query table found the foreign key still exist, and then delete the error: Causes and related solutions

1. CREATE TABLE test6: foreign key named 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. Delete the foreign key 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. Display the table again test6 found that the foreign key still exists 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 once again , found an error :

mysql> ALTER TABLE TEST6 drop foreign key test3_fk;

ERROR 1091 (42000): Can ' t DROP ' TEST3_FK '; Check that Column/key exists

The right solution :

1.show Index found with the same indexes as the foreign key name exists

+-------+------------+----------+--------------+-------------+-----------+------

-------+----------+--------+------+------------+---------+---------------+

| 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. Delete the index :

mysql> ALTER TABLE TEST6 DROP INDEX TEST3_FK;

Query OK, 0 rows affected (0.25 sec)

records:0 duplicates:0 warnings:0

3. Check the table again : There is no foreign key found

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)

The reasons are :

Obviously this is a bug in MySQL:
1.MYSQL after the foreign key is built, an index with the same name will be created automatically
2. While deleting the foreign key, if the same name index is not deleted, then MySQL thinks the foreign key still exists---MySQL will continue to show the foreign key in the show Keys command, and when you drop table, MySQL will continue to prompt you can ' t DROP ' TEST3_FK '; Check that Column/key exists "
3. Naturally, when you want to delete the foreign key that you see in the Show keys again, you will report 1091 errors, which is really like online, the foreign key name is wrong, because the foreign key is actually no longer exist. But it's a pity that you can see that the foreign keys are still there, whether from show keys or other table operations. That's misleading your eyes.

About MySQL: After the foreign key delete, the query table found the foreign key still exist, and then delete the error: Causes and related solutions