On the merits and demerits of the main foreign key of database

In the previous period of time to interview a large company is a formal, because there is work experience, without written directly into the technical interview, really is flying blind. Work, perhaps it is not self-motivated, just learned how to use programming to achieve the function, no time and not to consider performance problems, really failed, I am embarrassed to say that I am a programmer.

Analysis, the interview encountered problems. Foreign key of the database do you think it's useful? Encountering this problem, the moment I think of my work, in the design table, the table will be added to the foreign key, but when the SQL statement is generated, the foreign key is not consciously closed. The reason is that the foreign key will have constraints, then when the additions and deletions, there will always be constraints to verify the problem, is very offensive. Return to the topic, analysis of foreign key good and bad.

The advantages of the primary key is self-evident, easy to find and so on, who designed the database, the primary key is essential.

Good for foreign keys:

1. Reduce duplication of data. Before deleting the main table data, delete the related data of the related child table, in principle the data of the main table is not allowed to be deleted.

2, can set up cascade Delete and update, ensure the integrity of the database;

3, foreign key to a certain extent, explains the business logic, will make the design thoughtful and concrete comprehensive.

Ensure the consistency of data, integrity, the program can not 100% to ensure the integrity of the data, from this point of view, foreign keys seem to be necessary, the most important thing is the integrity of the data.

The foreign key is not good:

1, excessive emphasis or the use of primary key/foreign key will add to the development difficulty, resulting in too many problems such as the table;

2, without foreign key data management is simple, easy to operate, high performance (import and export operations, in the INSERT, UPDATE, delete data faster);

3. Data integrity can be ensured by trigger or application;

4, in the combination of hibernate use, difficult to control.

Other people's summary, for reference:

1, in large-scale systems (performance requirements are not high, high security requirements), the use of foreign keys, in large-scale systems (high performance requirements, security control), no foreign key; small system, the best use of foreign keys.
2, use the foreign key to be appropriate, can not be excessively pursued.
3, without foreign keys to control data consistency and integrity, you should write a layer to ensure that then each application through this layer to access the database.

Self-summary

Foreign key in the design table, still very important, can see the business logic of data, after all, Rome is not a day, after all, Oracle company in the design of the database, it is certain that the foreign key this thing to consider, personally think that negation is meaningless. But in my development experience, the design of foreign keys is a trap, add a burden, in the child table to add a field to place the foreign key, can not be. As for the uniqueness of the data, the Add field is used to judge.

(not finished, experience is shallow, summary may not be correct, still need to be amended)

On the merits and demerits of the main foreign key of database