Detailed explanation of whether the MySQL database should use foreign key constraints

1. Introduction

The topic of whether to use foreign key constraints is already a cliché. In school, most of what our teachers teach us is to establish foreign key constraints. However, in actual work, we often do not use foreign keys, but instead control them through code logic. Alibaba's JAVA specifications also clearly stipulate: [Mandatory] Foreign keys and cascades must not be used, and all foreign key concepts must be resolved at the application layer.

Why do we need to make such regulations? Should foreign key constraints be used? We can give an example to illustrate

2. Examples

Now we have created two tables in the database: [product and project]. The porduct field of [project] is associated with Product . There is a foreign key record between them as shown in the following figure:

When we add a record with project_id 1 to the [project] table, an error will be reported because there is no corresponding record in the [product] table:

It can be seen that the existence of this constraint will ensure the integrity of the relationship between data in the tables. It is less likely to have dirty data. This is a very obvious advantage of foreign key constraints!

To summarize, foreign key constraints have the following advantages:

• Ensure data integrity and consistency
• Convenient cascade operation
• The data integrity judgment is entrusted to the database to complete, reducing the amount of program code

But there are also disadvantages that cannot be ignored:

Performance issues

We have just created two tables, [project] and [product]. The [project] table has a foreign key constraint with the [product] table through the project_id field.

At this time, every time we insert data into the [project] table, it will first go to [product] to check whether there is corresponding related data. If it is controlled by the program, this query can be omitted. But if a foreign key constraint is set up, the query will definitely be performed. This is actually redundant. When there are few associated fields, there may be no impact, but once there are more associated fields, this impact will be particularly obvious!

Deadlock

Foreign keys cause queries to depend on other tables, which means InnoDB needs to check for corresponding values ​​in the parent table (or related tables). This also locks the row in the parent table to ensure that the row is not deleted before the transaction completes. This can lead to unexpected lock waits or even deadlocks, which are difficult to locate.

Difficulty in sharding

A database with constraints will be particularly difficult to use when it needs to be divided into different databases and tables.

Reduced development/testing efficiency

In our daily testing process, we often encounter situations where we find a BUG and want to reproduce it or facilitate testing. We will directly change the data in the database table to facilitate testing.

Although this is not standardized, the actual situation is that it can improve our efficiency a lot. This is beyond doubt! However, such operations may also bring some problems, such as bugs caused by data that are not actually program bugs, or some potential bugs cannot be discovered.

Conclusion

Currently, many Internet companies, especially large ones, require the disabling of foreign keys. This is not only due to performance issues, but also mainly because the Internet business changes quickly, which will indirectly lead to changes in the table structure. It is very likely that the existence of foreign key constraints will lead to unexpected problems and reduced development efficiency. Therefore, it is not recommended to use foreign key constraints in non-essential situations and business scenarios that do not require high reliability, so as to be more embracing of changes.
But we cannot simply dismiss it, because some business scenarios are better served by using foreign key constraints, such as government affairs, banking, military industry, etc., which require high data reliability. So my suggestion is: if the business is relatively complex, you can use foreign key constraints in the test environment, but they need to be removed in the production environment. If the business is relatively simple, you can completely delete the foreign key constraint. However, for scenarios such as banking and military industries where data errors are not allowed and high reliability is required, it is recommended to establish foreign key constraints.

This concludes this article on whether MySQL databases should use foreign key constraints. For more information about MySQL database foreign key constraints, please search for previous articles on 123WORDPRESS.COM or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!