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

This part mainly tells two parts, the first part is the reason that the programmer uses the foreign key and does not use the foreign key, the second part is the further reason analysis

1,Reasons to use Foreign Keys:

• You won ' t get orphaned Rows

• Can get nice ' on DELETE cascade ' behavior, automatically cleaning up tables

• Knowing about the relationships between tables on the database helps the Optimizer plan your queries for most efficient EX Ecution, since it is able to get better estimates on join cardinality.

• FKs give a pretty big hint on what statistics is most important to collect on the database, which in turn leads to better Performance

• They enable all kinds of auto-generated support--ORMs can generate themselves, visualization tools would be able to creat e Nice schema layouts for you, etc

• Someone new to the project would get into the flow of things faster since otherwise implicit relationships is explicitly d ocumented

2,Reasons not to use Foreign Keys:

• You is making the DB work extra on every CRUD operation because it have to check FK consistency. This can is a big cost if you had a lot of churn

• By enforcing relationships, FKs specify a order in which you had to add/delete Things,which can leads to refusal by the D B to does what want. (Granted, in such cases, what's trying to does is the create a orphaned Row, and that's not usually a good thing). This was especially painful when you were doing large batch updates, and you load up one table before another, with the Seco ND table creating consistent state (and should you are doing that sort of thing if there are a possibility that the second L Oad fails and your database is now inconsistent?).

• Sometimes you know beforehand your data are going to being dirty, you accept this, and you want the DB to accept it

• You are just being lazy:-)

3, people do not use foreign keys for further reasons analysis

If you is using a relational database then it seems you ought to having somere lationships defined in it. Unfortunately this attitude (you don't need foreign keys) seems to being embraced by a lot of application developers who Woul D rather not being bothered with silly things like data integrity (but need to because their companies don ' t has dedicated D Atabase developers). Usually in databases put together by these types is lucky just to has primary keys;)

Maybe it's a question of "parenting."

This is an issue of upbringing. If somewhere in your educational or professional career are spent time feeding and caring for databases (or worked closely With talented folks who do), then the fundamental tenets of entities and relationships is well-ingrained in your though T process. Among those rudiments is how/when/why to specify keys in your database (primary, foreign and perhaps alternate). It ' s second nature.

If,however, you ' ve not had such a thorough or positive experience in your past with rdbms-related endeavors and then you ' ve l Ikely not been exposed to such information. Or Perhaps your past includes immersion in an environment the was vociferously anti-database (e.g., "Those DBAs is idiot S-we Few, we chosen few java/c# code Slingers would save the day "), in which case you might being vehemently opposed to the Arcane Babblings of some dweeb telling you FKs (and the constraints they can imply) really is important if you ' d jus T listen.

Most everyone is taught when they were kids, brushing your teeth was important. Can you get by without it? Sure, but somewhere down the line you'll have less teeth available than you could has if you had brushed after every meal . If Moms and dads were responsible enough to cover the database design as well as oral hygiene, we wouldn ' t have had this conv Ersation. :-)

  

I ' m going to use the distilled "foreign keys is like brushing your teeth:go ahead, does without it, but careful when you s Mile "–mark Sowul

I believe that the real reason FKs constraints be used by some (most, from my perspective) is sheer laziness under th E pretense this they can defend their laziness with their performance savings argument. I firmly believe that the vast majority of the stupidity Expense we company incurs are due to lack enforcement of FK const Raints and the ripple effect that this has through acompany. Lack of Unique Keys is the other thing this drives me nuts next to 2000+ line stored procedures with levels of nested I Fs and random indenting,but I ' ll stop now. –chadd

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