Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Foreign Keys: Essential or Academic? Expand / Collapse
Author
Message
Posted Wednesday, February 12, 2014 8:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 12:52 AM
Points: 89, Visits: 286
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.
Post #1540801
Posted Wednesday, February 12, 2014 8:56 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:36 AM
Points: 2,836, Visits: 5,066

...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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1540813
Posted Wednesday, February 12, 2014 9:03 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 11:38 AM
Points: 588, Visits: 895
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
Post #1540818
Posted Wednesday, February 12, 2014 9:04 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 42,771, Visits: 35,870
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 2008, MVP
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

Post #1540820
Posted Wednesday, February 12, 2014 9:05 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 42,771, Visits: 35,870
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 2008, MVP
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

Post #1540821
Posted Wednesday, February 12, 2014 9:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Today @ 12:52 AM
Points: 89, Visits: 286
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 :)

Executive Junior Cowboy Developer, Esq.
Post #1540825
Posted Wednesday, February 12, 2014 9:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:36 AM
Points: 2,836, Visits: 5,066
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...


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

How to post your question to get the best and quick help
Post #1540826
Posted Wednesday, February 12, 2014 9:09 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 3:27 PM
Points: 21,631, Visits: 15,289
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1540829
Posted Wednesday, February 12, 2014 9:10 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 42,771, Visits: 35,870
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...


Can we compromise on expert beginner cowboy developer?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1540830
Posted Wednesday, February 12, 2014 9:10 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, June 26, 2014 4:08 AM
Points: 708, Visits: 3,286
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!

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
Post #1540831
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse