MySQL Simple Implementation of Ignoring Foreign Key Constraints When Deleting Tables

Deleting tables is not particularly common, especially for tables with foreign key associations. However, during the development process, it is common to find that there are problems in the design of Schema and to delete all the tables in the existing database to recreate them; In addition, during the test, all the tables in the database need to be recreated. Of course, many automation tools can also do such things.

When you delete a table, you sometimes encounter such an error message:

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 of other tables, so the table with foreign keys (child table) must be deleted before deleting this table (parent table). That is, the process of deleting a table needs to be the same as the process of creating a table.

However, this is often unacceptable. 1. If there are too many tables, manual sorting is a bit unacceptable; On the other hand, there is no automatic tool to sort pairs (in fact, it is not impossible). Therefore, a variable FOREIGN_KEY_CHECKS is provided in MySQL to set whether to check foreign key constraints if necessary.

1 Generally, it is recommended to do this:

First, automatically generate all 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 statement 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, it doesn't matter if you forget the last sentence. This variable is based on Session, which means that when you close the client and re-establish the connection, this variable will return to its default value. If you need to not check foreign key constraints globally (which will be rare), you can do this:

SET GLOBAL FOREIGN_KEY_CHECKS = 0;

Or

set @@global.FOREIGN_KEY_CHECKS = 0;