is the foreign key (Foreign key) constraint really necessary in the design of the database? A

It's been a while. I had a disagreement with my colleague about whether the database set foreign KEY constraints. I'm sticking with foreign key constraints and they're against using foreign key constraints. So, there was this article.

This article is mainly referring to the StackOverflow a topic on the website Is foreign keys really necessary in a databasedesign? ( http://stackoverflow.com/questions/18717/are-foreign-keys-really-necessary-in-a-database-design )

Own a little understanding, if there is not the right place, look correct!

The relational database occupies a considerable proportion in the actual use. A relational database can be abstracted into two parts: the relationship between entities, entities, and entities. Entity is an abstraction of reality, and the relationship between entity and entity is the relation between real things, and this kind of association generally plays a referential role. For example, in real life a user wants to place an order, so to create an "order" table, then this "order" table to rely on the "User" table ("Orders" table of the user ID, you may want to refer to the user ID in the table ). If you say that there is no such reference, it may happen that the "order" table has an order, but do not know who the user is, it would be very strange: no user orders, people are a little overwhelmed.

From a cognitive point of view, that reference relationship (referential integrity) is certainly needed. What should be done from the perspective of implementation ? There are two methods, one is to constrain from the data plane and the other is to constrain from the application level . How do you understand these two ways of implementation? From the data plane, is to establish a foreign key constraint on the database, when the data is stored or updated, the database to verify the integrity of the reference, this part of the work is done by the database engine. From the application level, it is a programmer-developed program to maintain the integrity of this reference, so that the program itself is good and bad or the quality of the programmer itself determines the referential integrity of the implementation of the quality, in other words, this is determined by the qualities of the person's work.

From a cognitive point of view, referential integrity is to be done, but from the perspective of implementation, what should be the way? One is done by the engine of the database, a program written by a programmer. I think this answer should be one-sided, of course, by the engine of the database. Then why do you say that?

Foreign keys help enforce referential integrity at the Datalevel . FOREIGN KEY constraints ( Foreign key Constraint ) from the data plane ( Data Level ) guarantees the integrity of the reference ( referentialintegrity ).

If it is done by a programmer, then there is a premise: the programmer will not make mistakes in the program. People with a little bit of common sense will know that human error is inevitable, especially for programmers. Therefore, the idea that "referential integrity is maintained by programmers" is unreliable. In large enterprises, the same database is often accessed by multiple applications. If this referential integrity is maintained by a programmer, then in principle each application should have its own approach to ensure this referential integrity, which will undoubtedly increase the workload of the development, otherwise it may cause garbage data to be generated in the database.

"Supposea programmer is actually doing this in the right manner already", Making such asupposition seems to me-be-an ex Tremely Bad idea; In general software isphenomenally buggy.

Andthat ' s the point, really. Developers can ' t get things right, so ensuring thedatabase can ' t being filled with bad data is a good Thing.  

and referential integrity between data should be independent of any application, in other words, without any application, adding an application to the database or reducing an application without compromising the integrity of the data reference.

tonyandrews "I never hit a pole, but I think it's better to wear a seatbelt," he said.

Sofar, I ' ve never driven into a lamp-post. But I still think it's a good idea towear seat belts;-) –tony Andrews

external key constraint ( FK Constraint ) and a seat belt for comparison.

Adatabase schema without FK constraints is like driving without a seat belt. Oneday, you ' ll regret it. Not spending this little extra time on the designfundamentals and data integrity are a sure fire to assuring Later.

Foreign keys can improve performance.

Theyalso improve performance because they ' re normally indexed by default.

foreign keys can improve performance,SQL Server is an exception.

Otherdatabases may automatically create indexes to foreign keys, but SQL serverdoes not. In SQL Server, foreign key relationships is only constraints. Youmust defined your index on foreign keys separately (which can is of benefit.)

Foreign keys can show the relationships between different data tables so that others are familiar with them as soon as possible.

Withouta foreign Key How do I tell that, records in different tables is related?

ForeignKeys allow someone the who have not seen your database before to determine therelationship between tables.

Everythingmay is fine now, but think what would happen when your programmer leaves Andsomeone else have to take over.

Foreign Keys would allowthem to understand the database structure without trawling through thousand oflines of code.

is the foreign key (Foreign key) constraint really necessary in the design of the database? A