The database should not use a foreign key, and the database should not

(Switch) The database should not use a foreign key, and the database should not

The foreign key makes many programmers love and hate it. Although it ensures the integrity of the database efficiently, it once made us feel so happy to have it and maintain the database, we do not need to write so much tedious code to ensure data integrity. However, as our system grows, we find that it has brought us a lot of trouble, and the complexity and performance have been affected, so we have a question about whether or not to use the foreign key.

Whether we do program design, all walks of life in our society, or everything, there is a degree of constraint. When I went to school, I always said that things have two sides in politics. The good side has one, and the bad side also has one. Therefore, the foreign key cannot escape this path. In my opinion, any technology has the value of its existence. We may not find it, but it does not mean it does not exist. The same is true for its shortcomings. This is what we all understand when it comes to linking theory with practice. simply saying that a technology is good or bad and is out of practical application is one-sided. A little more. Let's talk about the foreign key.

Let's talk about the advantages of Foreign keys:

1. through Foreign keys, the database itself can ensure data integrity and consistency. Although we can write some code in the program for maintenance, the program can never guarantee data integrity and consistency by 100%. The foreign key itself is part of the database, so when the database server encounters a problem, it can also save the integrity and consistency of the database to the maximum extent.

2. databases with foreign keys can make ergraphs more readable, and the relationship between tables in the database is clearer. This is also useful for secondary development or maintenance of the system.

3. In terms of business logic, the foreign key can be used to better understand the business logic and play an auxiliary role in the design of functions, so that the design is more thoughtful and complete.

4. for a project, developers are constantly changing and their capabilities are not hierarchical. Therefore, it is even more difficult to ensure data integrity through code, with foreign keys, developers are constrained to some extent to avoid data integrity caused by some low-level errors.

 

There are also the following Disadvantages:

1. Excessive use of foreign keys will make the system development more difficult and cause too many tables, increasing the complexity of the system.

2. performance problems because foreign keys have certain constraints on the database, we must verify this constraint each time we perform database operations. There is no problem for systems with small data volumes, however, when the data changes significantly, the performance disadvantage is obvious.

 

Obviously, we still need to analyze the foreign key application based on specific problems. What benefits will the use of this system bring to us? We don't need to bring any harm to the system. If there is more, then our choice will naturally be clear. In fact, we all know how to analyze the advantages and disadvantages, or whether the foreign key is more advantageous or more harmful. In my opinion, when we start a project, we should analyze the foreign key constraints from the following aspects:

1. complexity of project business logic

Business logic is actually the most fundamental thing of a project. It is a type of the project. It is like a main line that runs through the beginning and end of the project. Therefore, when the business logic is very complex, the relationships between entities are also very ambiguous. The reason for this is that sometimes the relationships between entities are intricate, many are associated. At this time, the foreign keys help us to clarify the relationship between them, and at the same time, it is easier to use the foreign keys in the project to ensure data integrity and consistency. Due to the complexity of the relationship, we have no way to use a program to 100% ensure data integrity and consistency. On the contrary, if the business logic is not complex and the relationship is very clear and similar to the "one-man-one-wife System", we can ensure the integrity and consistency in the program, of course, there is no need to use foreign keys.

2. Schedule of the project

Programmers are very mobile. Maybe when a project is half developed, all the programmers in the entire project team except the Project Manager are replaced. If the documents are not kept up with in time, or it is unclear during the handover, which is very dangerous for the project. Although experienced programmers may think about integrity during coding, for various reasons, they have no way to ensure data integrity and consistency through programs 100%. The experience is still the same, so at this time, the foreign key completes the last level for the integrity of the project, whether they are familiar with the project or not, on the integrity and consistency issues, the database itself is fully prepared. On the contrary, if the project has a short cycle and personnel changes are small, you can leave this factor aside and consider whether to use foreign keys from other aspects.

3. Security and Integrity

In fact, it is best to understand this problem. If the project has extremely strict requirements on data security, integrity, and consistency, it must use foreign keys, and some performance may be missing. Even this is worthwhile, this satisfies the project's needs to the greatest extent possible. This is the key.

4. Performance

The performance has been discussed in the third part. For large systems, if millions of data operations are performed every day, the performance becomes fatal if foreign keys are used. Foreign keys are a constraint. Each operation of insert, update, or delete must use this constraint to verify data integrity and consistency. In this case, the performance loss may be several hours or even dozens of hours.

The above points are summarized by myself. Maybe more experienced people will have other considerations. In short, technology is constantly evolving and many new technologies are born every day, after learning about them, we should think more about where they apply.

 

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/yangzhongwei1031/archive/2010/04/07/5459583.aspx