SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Foreign Keys: Essential or Academic?


Foreign Keys: Essential or Academic?

Author
Message
Xedni
Xedni
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1829 Visits: 742
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?

Executive Junior Cowboy Developer, Esq.
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12330 Visits: 5478

...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...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Keith Tate
Keith Tate
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2306 Visits: 979
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.



Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
GilaMonster
GilaMonster
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222937 Visits: 46294
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222937 Visits: 46294
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Xedni
Xedni
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1829 Visits: 742
Thank you all for the quick and thorough responses. The assertion that they weren't essential literally kept me awake last night. I think I'll rest easier knowing that I'm doing the right thing by continuing to use them Smile

Executive Junior Cowboy Developer, Esq.
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12330 Visits: 5478
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

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)SSC Guru (66K reputation)

Group: General Forum Members
Points: 66261 Visits: 18570
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

GilaMonster
GilaMonster
SSC Guru
SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

Group: General Forum Members
Points: 222937 Visits: 46294
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? Cool

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Abu Dina
Abu Dina
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2955 Visits: 3325
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

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search