Why doesn't mysql allow me to delete foreign keys?

mysql does not allow deletion of foreign keys because after creating a foreign key, an index with the same name will be automatically created. When deleting the foreign key, if the index with the same name is not deleted, mysql will think that the foreign key still exists. , then mysql will continue to display the foreign keys in the show keys command.

#Sometimes we cannot delete mysql foreign keys, what should we do? Below I will introduce to you why mysql does not allow foreign keys to be deleted and the solution. I hope it will be helpful to friends who need it!

1. After MYSQL creates a foreign key, it will automatically create an index with the same name

2. When deleting the foreign key, if the index with the same name has not been deleted, MYSQL considers the foreign key Still exists---MYSQL will continue to display foreign keys in the show keys command. When you drop the table, MYSQL will continue to prompt you "a foreign key constraint fails", that is, the foreign key constraint fails

3. Naturally , when you want to delete the foreign key you see in show keys again, a 1025 error will be reported. This is indeed as said on the Internet, the foreign key name is wrong, because in fact the foreign key no longer exists. But unfortunately, no matter from You can see that the foreign key still exists through show keys or other table operations, which misleads your eyes

Solution:

1. Delete the index first

mysql> show index from tableName;
mysql> alter table tableName drop index FKxxxxxxxxxxxxxx;

2. Delete the foreign key again

mysql> show keys from tableName;
mysql> alter table tableName drop foreign key FKxxxxxxxxxxxxxx;

If that doesn’t work, you can try to delete the foreign key first, then delete the index, or reconnect to the database.

Related recommendations: "MySQL Tutorial"

The above is the detailed content of Why doesn't mysql allow me to delete foreign keys?. For more information, please follow other related articles on the PHP Chinese website!