introduction
In fact, this topic is a cliché, and many people really do not use foreign keys at work. Included in Ali's JAVA specification, there is also the following article
[Mandatory] Foreign keys and cascading must not be used, and all foreign key concepts must be resolved at the application layer.
However, when asked why, most of them answered like this
Foreign key constraints must be taken into account every time DELETE or UPDATE is done, which can lead to painful development and extremely inconvenient test data.
Frankly, that's also true. However, it is not comprehensive enough, so I will open an article to elaborate.
body
First of all, let's make it clear that a foreign key constraint is a constraint, and the existence of this constraint will ensure that the relationship between the tables and the data is "always complete". Therefore, the existence of foreign key constraints is not entirely without merit.
For example, using foreign keys, yes
- Guarantee data integrity and consistency
- Cascading operation is convenient
- Entrusting data integrity judgments to the database reduces the amount of code in the program
However, fish and bear paws cannot be combined. Foreign keys are able to ensure the integrity of the data, but will bring many defects to the system. It is precisely because of these defects that we do not recommend the use of foreign keys, as follows
Performance issues
Suppose a table is named user_tb. Then there are two foreign key fields in this table, pointing to two tables. Then, every time you insert data into a user_tb table, you must query the table corresponding to the two foreign keys for the corresponding data. If it is left to the control of the program, this query process can be controlled by us, and some unnecessary query processes can be omitted. But if it is controlled by the database, it must be judged in these two tables.
Concurrent issues
In the case of using foreign keys, each time you modify the data, you need to go to another table to check the data, and you need to acquire additional locks. In high concurrent high-traffic transaction scenarios, using foreign keys is more likely to cause deadlocks.
Extensibility issues
Here is mainly divided into two points
- It is convenient to do platform migration, such as you migrate from Mysql to Oracle, things like triggers and foreign keys can be implemented by using the characteristics of the framework itself, without relying on the characteristics of the database itself, and it is more convenient to do migration.
- Sharding is convenient for sharding tables, and in the case of horizontal splitting and sharding, foreign keys cannot take effect. Put the maintenance of the relationship between the data into the application, saving a lot of trouble for future database sharding tables.
Technical issues
By using foreign keys, the judgment logic that the application should perform is actually transferred to the database. This means, then, that the performance overhead of the database becomes larger, and then the requirements for the DBA are even higher. Many small and medium-sized companies do not employ professional DBAs due to financial problems, so they will choose not to use foreign keys to reduce database consumption.
Conversely, if the constraint logic is found in the application and the application server performance is not enough, you can add machines and do horizontal scaling. If you are on a database server, the database server will become a performance bottleneck, and horizontal scaling is difficult.
I spent 2 months to sort out a set of JAVA development technical data, covering Java basics, distributed, microservices and other mainstream technical materials, including large factory face experience, learning notes, source code handouts, project practice, and explanatory videos.
Hope to help some friends who want to improve their abilities through self-study, get information, scan the code and pay attention to it
Remember to forward + follow + private message
Private Message Reply【2022 Interview Information】
Receive more learning materials