Simple implementation of ignoring foreign key constraints when deleting MySQL tables

Deleting a table is not very common, especially for tables with foreign key associations, you need to be more careful when deleting. However, during the development process, it is common to find problems with the schema design and to delete all tables in the existing database and recreate them. In addition, during testing, it is also necessary to recreate all tables in the database. Of course, many automated tools can also do this.

When deleting a table, you may encounter an error message like this:

ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

This is because the fields in the table you are trying to delete are used as foreign keys in other tables, so you must delete the table with the foreign key (child table) before deleting this table (parent table). That is to say, the process of deleting a table needs to be consistent with the process of creating a table.

But this is often unacceptable. On the one hand, if there are too many tables, manual sorting is a bit unacceptable; on the other hand, there is currently no automatic tool to sort them (in fact, it is not impossible to achieve). Therefore, MySQL provides a variable FOREIGN_KEY_CHECKS to set whether to check foreign key constraints when necessary.

It is generally recommended to do this:

First, automatically generate all the DROP statements and replace MyDatabaseName with your database name:

SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'MyDatabaseName';

Then, add the following statements to set the FOREIGN_KEY_CHECKS variable before and after the generated code:

SET FOREIGN_KEY_CHECKS = 0
-- DROP statement SET FOREIGN_KEY_CHECKS = 1;

However, if you forget the last sentence, it doesn’t matter much. This variable is based on the Session, that is, when you close the client and reconnect, this variable will return to the default value. If you need to not check foreign key constraints globally (this situation is relatively rare), you can do this:

SET GLOBAL FOREIGN_KEY_CHECKS = 0;

or

set @@global.FOREIGN_KEY_CHECKS = 0;

The above simple implementation of ignoring foreign key constraints when deleting MySQL tables is all the content that the editor shares with you. I hope it can give you a reference. I also hope that you will support 123WORDPRESS.COM.