February 12, 2014 at 8:47 am
Hi SSC,
I'd like to elicit some input from the community about foreign key usage in real systems.
Where I work, for one reason or another, we hardly use foreign keys, primarily relying on vendors and applications to provide data with integrity, and for code to handle when referential integrity is broken.
Yesterday, a senior database developer colleague of mine described the use of foreign keys as "purely academic" citing that FK's provide little benefit that can't be enforced through procedural or application logic, thus freeing up database developers to affect code changes more quickly.
I have only been developing databases for about 3 years now, so my real life experiences are not sufficient to refute or concede to his point. Can anyone who has used FK's extensively (and ideally who also has experience with databases without them) lend me their thoughts on this topic?
February 12, 2014 at 8:56 am
...a senior database developer colleague of mine described the use of foreign keys as "purely academic" citing that FK's provide little benefit that can't be enforced through procedural or application logic, thus freeing up database developers to affect code changes more quickly...
Should his full title to be change to "Senior database Cowboy-developer" ? 😉
Do you really think that Relationship constraints (eg FK's) do exist in relational database systems such as SQL Server, Oracle and all other I know about, just to please some academics?
To compliment your senior colleague opinion for " freeing up database developers to affect code changes more quickly" you can suggest him that having non-production environments is also pure academic idea. You can be much more effective making fixes and changes straight in production, significantly cutting time your users waiting for fixes and new functionality...
February 12, 2014 at 9:03 am
I almost always have foreign keys in my relation design and not just for academic reasons. This will guarantee that I won't get orphaned records, if you use an ORM tool FKs are a must (in my opinion), new developers can get a hold on the schema and relationships faster being able to check the keys, the Optimizer can use FKs to create better execution plans, etc.
There are also reason not to add FKs, but citing they are "purely academic" is just lazy in my opinion.
February 12, 2014 at 9:04 am
Essential. Absolutely essential.
There's no procedural or application code that will stop someone from updating a table directly if they have permissions, or stop a newly written app from putting bad data in because those application constraints have been ignored or forgotten (or because they're too time consuming to write)
I cannot think of a case where I've been told that 'the application enforces relationships' and the data is accurate. Every single time I've seen that (and it's been a number of times), there's been garbage data in the database that violates the relationships which should exist.
Foreign keys also provide performance benefits by giving the optimiser additional information which it can use to generate query plans.
I can't understand why he claims implementing relationships manually in the application (hence writing code to do so) could possibly be easier or less time consuming than writing a foreign key constraint once.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2014 at 9:05 am
Eugene Elutin (2/12/2014)
...a senior database developer colleague of mine described the use of foreign keys as "purely academic" citing that FK's provide little benefit that can't be enforced through procedural or application logic, thus freeing up database developers to affect code changes more quickly...
Should his full title to be change to "Senior database Cowboy-developer" ? 😉
Junior cowboy developer I suspect. A senior would know better than this.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2014 at 9:07 am
February 12, 2014 at 9:08 am
GilaMonster (2/12/2014)
Eugene Elutin (2/12/2014)
...a senior database developer colleague of mine described the use of foreign keys as "purely academic" citing that FK's provide little benefit that can't be enforced through procedural or application logic, thus freeing up database developers to affect code changes more quickly...
Should his full title to be change to "Senior database Cowboy-developer" ? 😉
Junior cowboy developer I suspect. A senior would know better than this.
Will argue to death on this:
"Senior database Cowboy-developer"!
A junior one, would have no guts to advise others on this...
:hehe:
February 12, 2014 at 9:09 am
GilaMonster (2/12/2014)
Eugene Elutin (2/12/2014)
...a senior database developer colleague of mine described the use of foreign keys as "purely academic" citing that FK's provide little benefit that can't be enforced through procedural or application logic, thus freeing up database developers to affect code changes more quickly...
Should his full title to be change to "Senior database Cowboy-developer" ? 😉
Junior cowboy developer I suspect. A senior would know better than this.
A senior should know better - but alas what's in a title?;-)
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 12, 2014 at 9:10 am
Eugene Elutin (2/12/2014)
GilaMonster (2/12/2014)
Eugene Elutin (2/12/2014)
...a senior database developer colleague of mine described the use of foreign keys as "purely academic" citing that FK's provide little benefit that can't be enforced through procedural or application logic, thus freeing up database developers to affect code changes more quickly...
Should his full title to be change to "Senior database Cowboy-developer" ? 😉
Junior cowboy developer I suspect. A senior would know better than this.
Will argue to death on this:
"Senior database Cowboy-developer"!
A junior one, would have no guts to advise others on this...
:hehe:
Can we compromise on expert beginner cowboy developer? 😎
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2014 at 9:10 am
JeeTee (2/12/2014)
Hi SSC,I'd like to elicit some input from the community about foreign key usage in real systems.
Where I work, for one reason or another, we hardly use foreign keys, primarily relying on vendors and applications to provide data with integrity, and for code to handle when referential integrity is broken.
Yesterday, a senior database developer colleague of mine described the use of foreign keys as "purely academic" citing that FK's provide little benefit that can't be enforced through procedural or application logic, thus freeing up database developers to affect code changes more quickly.
I have only been developing databases for about 3 years now, so my real life experiences are not sufficient to refute or concede to his point. Can anyone who has used FK's extensively (and ideally who also has experience with databases without them) lend me their thoughts on this topic?
Would you trust a person with no integrity? Thought not...
Same goes for databases with no RI constraints.
Funny you should mention this topic. I started working on a data migration project for a large UK based charity recently. Guess what I found? .... a 300GB database with no RI constraints! So my first deadline was to present an ERD of the existing database. Easy so I thought.... just point schema spy at the database and let it work it's magic. Kabooom! nothing.... have to do it all manually and try and figure out all the PKs and FKs myself. Within 5 minutes I was finding orphaned records all over the shop. Madness! :w00t:
I've seen this in various places I've worked at. Front-end developers given responsibility to develop back-end database as well. It just doesn't work!
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
February 12, 2014 at 9:14 am
GilaMonster (2/12/2014)
Essential. Absolutely essential.There's no procedural or application code that will stop someone from updating a table directly if they have permissions, or stop a newly written app from putting bad data in because those application constraints have been ignored or forgotten (or because they're too time consuming to write)
I cannot think of a case where I've been told that 'the application enforces relationships' and the data is accurate. Every single time I've seen that (and it's been a number of times), there's been garbage data in the database that violates the relationships which should exist.
Agreed. I have seen thus numerous times as well.
Foreign keys also provide performance benefits by giving the optimiser additional information which it can use to generate query plans.
I can't understand why he claims implementing relationships manually in the application (hence writing code to do so) could possibly be easier or less time consuming than writing a foreign key constraint once.
There is a great example on Grant Fritcheys blog showing the benefit to the optimizer and how FKs can help.
http://www.scarydba.com/2010/11/22/do-foreign-key-constraints-help-performance/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
February 12, 2014 at 9:16 am
GilaMonster (2/12/2014)
Eugene Elutin (2/12/2014)
GilaMonster (2/12/2014)
Eugene Elutin (2/12/2014)
...a senior database developer colleague of mine described the use of foreign keys as "purely academic" citing that FK's provide little benefit that can't be enforced through procedural or application logic, thus freeing up database developers to affect code changes more quickly...
Should his full title to be change to "Senior database Cowboy-developer" ? 😉
Junior cowboy developer I suspect. A senior would know better than this.
Will argue to death on this:
"Senior database Cowboy-developer"!
A junior one, would have no guts to advise others on this...
:hehe:
Can we compromise on expert beginner cowboy developer? 😎
Deal! 😀
(How dare was I to argue with you... )
February 12, 2014 at 9:26 am
If this is an OLTP system, absolutely use FKs. However, there are some highly respected data warehouse pundits out there that believe foreign keys, in an OLAP fact table and in a production environment, are not necessary because they add overhead for checks that should have been done in the staging area.
However, they also acknowledge any OLAP/DWH system, no matter how well it is designed, is subject to mistakes, and therefore referential integrity violations. In all my dealings, I would say in only TWO of those engagements did the production data warehouse NOT have foreign keys enabled in their fact tables.
We are human. We make mistakes. FKs save us from ourselves at the cost of some developer conveniences, however, I do not share the same passion as some other folks on this issue. Each situation and environment is unique, and the most important thing is to keep an open mind about things. No one is walking in your shoes but you, but for someone to make a statement such as "FKs only exist for academic purposes" may want to have a backup career plan.
February 13, 2014 at 4:34 am
GilaMonster (2/12/2014)
Essential. Absolutely essential.I can't understand why he claims implementing relationships manually in the application (hence writing code to do so) could possibly be easier or less time consuming than writing a foreign key constraint once.
Because when FK's are enforced his code just keeps generating tiresome errors, which slow him down?
I have, in the past, had complaints that app code was failing due to fk's, and "could I take the foreign keys out?".
It must be a while since that happened though. I can't even remember where they're buried.
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
February 13, 2014 at 5:07 am
I'm going to be the lone guy here and go out on a limb. First, in a new database or even a developing database from a business perspective, yes absolutely essential. Second, from an existing database that is deployed to hundreds of servers and ranges in size from 1000 transactions per month to millions per month, maybe not so essential.
Here's my reasoning: I worked on a healthcare billing application that was deployed to hundreds of individual clients that was developed entirely without foreign keys or check constraints. The developers built into the logic of both the application and the business logic in the DB ways to find almost every orphan and resolve it by the user. Keep in mind that most healthcare billing applications are not OLAP or OLTP, they are a combination of both in order to give the proper information with speed to the small number of users. So each user can handle about 100x the number of patients than a traditional (green screen is considered traditional) or paper based systems. Retrofitting that database to fully support FKs without big issues with the litterally hundreds of backend processes breaking would take several years work to do.
Like I said before, if you can do FKs, do them. New development, Do them. If you are given a highly complex system involving 700 tables of mixed OLAP and OLTP and more than 5000 stored procedures that is deployed to hundreds of client servers and has hundreds of background processes that both manipulate the data (through stored procedures largely)... you might want to take a much more measured approach as any change will result in hundreds of calls to your support center (just moving a delete button from the bottom right of the application grid to the bottom left resulted in over 100 calls complaining that we removed the delete function... we had to do special training and add KB articles, and a ton of other things in order to simply move a button to the left).
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply