Simple implementation of ignoring foreign key constraints when deleting MySQL tables

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.

You may also be interested in:
  • Specific method to add foreign key constraints in mysql
  • MySQL foreign key constraint (FOREIGN KEY) case explanation
  • Summary of MySQL foreign key constraints and table relationships
  • Example explanation of MySQL foreign key constraints
  • Detailed explanation of MySQL foreign key constraints
  • Introduction to MySQL method of deleting table data with foreign key constraints
  • How to disable foreign key constraint checking in MySQL child tables
  • How to create and delete foreign key constraints in MySQL

<<:  Specific use of exception filter Exceptionfilter in nestjs

>>:  Super detailed steps to install zabbix3.0 on centos7

Recommend

Several ways to easily traverse object properties in JS

Table of contents 1. Self-enumerable properties 2...

Simple implementation method of vue3 source code analysis

Table of contents Preface 🍹Preparation 🍲vue3 usag...

Some common properties of CSS

CSS background: background:#00ffee; //Set the back...

Explanation of the steps for Tomcat to support https access

How to make tomcat support https access step: (1)...

Enterprise-level installation tutorial using LAMP source code

Table of contents LAMP architecture 1.Lamp Introd...

In-depth explanation of hidden fields, a new feature of MySQL 8.0

Preface MySQL version 8.0.23 adds a new feature: ...

Example of how to quickly build a LEMP environment with Docker

LEMP (Linux + Nginx + MySQL + PHP) is basically a...

MySQL 5.7.17 winx64 installation and configuration tutorial

Today I installed the MySQL database on my comput...

mysql trigger creation and usage examples

Table of contents What is a trigger Create a trig...

JS implements multiple tab switching carousel

Carousel animation can improve the appearance and...

HTML dl, dt, dd tags to create a table vs. Table creation table

Not only does it reduce the cost of website develo...

Comprehensive understanding of line-height and vertical-align

Previous words Line-height, font-size, and vertica...

Idea deploys remote Docker and configures the file

1. Modify the Linux server docker configuration f...

Advanced explanation of javascript functions

Table of contents Function definition method Func...