The database should not use foreign keys

Foreign key This thing makes how many programmers love and hate, although the efficient guarantee of the integrity of the database, once let us feel that with it, to maintain the database is how happy one thing, we do not need to write so many cumbersome code to ensure the integrity of the data. But as our system grew larger, we found that it brought us a lot of trouble, complexity, performance are affected, so we have the question of whether the foreign key should not be used.

Whether we do programming, or in the community, or any one thing is a degree of restraint. School when the political class is always said that things are two-sided, the good side has, of course, the bad side also has, so the foreign key this thing naturally can not escape this way. In my opinion, any technology has the value of its existence, perhaps we have not found, but does not mean that it does not exist, the same is the disadvantage. Theory with practice, this is we all understand the truth, blindly say that a technology good or bad and out of the actual application of the speech, it is one-sided. Said a bit more, the following to talk about the foreign key this thing.

Let's talk about the advantages of foreign keys :

1. Through the foreign key, the database itself can guarantee the integrity and consistency of the data, although we can write some code in the program to maintain, but the program always has no way to ensure the integrity and consistency of the data 100%. The outside key itself is part of the database, so when the database server problems, it can also maximize the integrity and consistency of the database.

2. A database with a foreign key can make ER diagrams more readable, and the relationship between tables in the database is more straightforward, which is also useful for two development or maintenance of the system.

3. In the business logic, through the foreign key can be more intuitive understanding of business logic, in the design of functions played an auxiliary role, so that the design more thoughtful and perfect.

4. For a project, the developer is always constantly changing, the developer's ability is also not level, then through the code to ensure data integrity is even more difficult things, with a foreign key to a certain extent constrained the developer, to avoid some low-level errors resulting in incomplete data.

As for the disadvantages , there are the following points:

1. Excessive use of foreign keys will make the system more difficult to develop, but also lead to too many tables, increasing the complexity of the system.

2. Performance problems, because the foreign key to the database has a certain constraint, then every time we do database operation must verify this constraint, for the data volume of the system without any problems, but when the data becomes very large, then the performance of the disadvantage is very obvious.

Obviously, we still have to analyze the application of foreign key according to the specific problem. With what will give us the benefits of this system, do not have to bring any harm to the system, which more, then our choice is naturally clear. In fact, we all understand how we should analyze good and bad, or how to see the use of foreign keys when the advantages of a little more or more harm. I think that when we start to do a project, we should analyze the SISU key constraint from the following aspects :

1. Complexity of the business logic of the project

Business logic is actually a project the most fundamental thing, is a project of a nuclear type, it is like a main line, throughout the project. So when the business logic is very complex, then the relationship between the entities is very "ambiguous", the reason why the "ambiguous" is because sometimes the relationship between the complex, there are many associations. This time the foreign key just helps us to clarify their relationship, while the use of foreign keys in the project to ensure data integrity and consistency. Because of the complexity of the relationship, we have no way to use the program to 100% guarantee the integrity and consistency of the data. Conversely, if the business logic is not complex, the relationship is very clear, approximate to "monogamy", then we can guarantee the integrity and consistency in the program, of course, there is no need to use foreign keys.

2. Time of the project plan

The programmer's liquidity is very big, perhaps a project in half, the whole project team except the project manager outside the programmer all substitutions, if the document is not in time to keep up, or the handover is unclear, it is very dangerous for the project. For programmers who have been killed halfway through, although experienced programmers think about completeness when coding, they have no way to ensure the integrity and consistency of the data 100% through the program for a variety of reasons. Experienced even so, so this time the foreign key for the integrity of the project to the last level, regardless of their familiarity with the project, in the integrity and consistency of the problem above, the database itself has been well prepared. On the contrary, if the project has a short period and a small change of staff, it is possible to put aside this factor and consider, in other respects, the need to use foreign keys.

3. Security and integrity

This problem is best understood, if the project to the data has a very stringent security, integrity and consistency requirements, it is necessary to use foreign keys, may be missing a part of the performance, even if it is worthwhile, so as to maximize the needs of the project, this is the key.

4. Performance

The above 3rd has discussed the performance of the problem. For large-scale systems, if you have millions data operations on a daily basis, then if you use foreign keys, then performance becomes a fatal problem. A foreign key is a constraint, and we use this constraint to verify that the data is intact and consistent every time we operate an INSERT, update, or delete. The lack of performance at this time could be hours or even dozens of of hours.

The above points are my own summary, perhaps more experienced people will have other considerations, in a word, technology is constantly developing, every day there are many new technologies are born, after we understand them more should be to think about where they apply to the occasion.

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

(go) The database should not use foreign keys