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

Foreign Keys are evil? Expand / Collapse
Author
Message
Posted Tuesday, February 25, 2014 9:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 26, 2014 7:39 AM
Points: 1, Visits: 9
Sorry for posting under an assumed name but I have to keep it anonymous due to corporate restrictions and generally to protect the innocent.

I've been a professional developer for about 18 years and though I'm not a DBA I've worked closely with them over the years and have formed what I feel is a pretty decent sense of what are and are not good database practices. I just joined a company where two developers are in charge of the database schema and I found they are very much opposed to the use of foreign key constraints.

Their reasoning as best as I can tell is that
(1) it makes unit testing stored procedures more difficult due to the extra data setup involved and
(2) foreign keys can raise errors as order is important. They would actually prefer orphaned data rather than stoppage of the application.

This seems like bad practice to me but they are unwavering in their position. We've brought up advantages foreign keys provide in terms of data integrity, query performance, generating database diagrams, etc, to no avail.

Am I not seeing something here? Any advice?
Post #1544997
Posted Tuesday, February 25, 2014 9:28 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 3:57 PM
Points: 120, Visits: 677
Nothing really to see, they just prefer things to be easy than to be done the right way. If they don't care about how accurate data is then lose the foreign keys and let the inmates run the asylum. The way you're doing it is the way you should be doing things, if you don't have foreign key constraints than many other unintended things can crop up and create a huge cluster ****

+1 for Foreign Keys :)
Post #1545002
Posted Tuesday, February 25, 2014 9:33 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 3,374, Visits: 7,300
frankincognito404 (2/25/2014)
two developers are in charge of the database schema

That explains everything
Foreign keys are tools to mantain and ensure data integrity. Not evil but all the other way around.
You could check the following thread very similar to this one: http://www.sqlservercentral.com/Forums/Topic1540801-391-1.aspx



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1545008
Posted Tuesday, February 25, 2014 9:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, May 5, 2014 6:31 AM
Points: 291, Visits: 519
As always... It depends

If you have an OLTP like a sales web site, RI is critical to avoid loosing customers information.

On the analytical side, is is common to drop RI or load orphan records to a dummy key as it is more important to know that product was sold than who it was sold to.

99.999% of the apps fall somewhere in the middle.

As far as testing, I never let the argument of difficulty interfere with the need to do things correctly. Far too often, developers use good test data and never test anomalies. Yes, it takes more time, but if you are not going to test properly, then why test at all?
Post #1545009
Posted Tuesday, February 25, 2014 9:52 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:40 PM
Points: 804, Visits: 1,990
If it's been like this for a while, you should be able to find orphaned data to help make your case. It still probably won't convince them, at which point you need to decide if you need to bring it to someone else's attention.


Post #1545024
Posted Wednesday, February 26, 2014 10:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:06 AM
Points: 1,166, Visits: 1,192
stormsentinelcammy (2/25/2014)
Nothing really to see, they just prefer things to be easy than to be done the right way.

+1 for Foreign Keys :)

+1 Agree 100%

Rob
Post #1545481
Posted Wednesday, February 26, 2014 11:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 5:13 PM
Points: 33,100, Visits: 15,210
In terms of testing, building some setup harnesses for FK data shouldn't be too hard. It's also reuseable work, so that doesn't seem to be a valid argument for anything other than laziness.

However, if orphaned data isn't an issue for the business, then it isn't. If customers can re-enter information, and it doesn't impact business, then it's inefficient, but not wrong.

I'd argue the FKs prevent issues, and I've seen that in most applications, but potentially it isn't an issue, or less of an issue, than the application reporting errors to users. If that's the case, I'd make sure I had queries and checks written to look for orphaned or semi-lost data so I could find it if someone raises an issue.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1545504
Posted Thursday, February 27, 2014 6:23 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 5:48 AM
Points: 906, Visits: 1,419
Depending on your organizational structure, I would also mention that it really isn't their decision to make; it's more of a business decision.

Does the business know of
1. the existence of orphaned records and how it is affecting the data integrity
2. the potential of orphaned records and how that might affect the data integrity
3. Are the business rules being enforced?

I've seen the argument for not using FK's and enforcing business rules in the application, as well as parent/child relationships. For me, I haven't been convinced that it's better to not use FK's. One of our core responsibilities as a DBA is to protect the integrity of the data. We need to do everything within our power to do so, but due to business requirements do not always "win", but at the very least, the business is aware of the ramifications of not enforcing rules. I'd also document their decision.

Even still, on a number of occasions I've advised the business that something may need to be enforced or tables/schemas should be set up a certain way to prevent future problems or to provide for future features and they said they cannot see the situation where it was necessary. Then a year or two later, they want the recommendation and even though it was documented, "rework" had to be done.

Do you have the support of your manager? What are the database and application standards at your organization? If you don't have any database standards, consider writing some up and presenting them to your manager and the team.

The best you can do is maintain your integrity by making the case to the business and documenting the results.

Best of luck,
Steve




Post #1545830
Posted Thursday, February 27, 2014 6:23 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:53 AM
Points: 15,560, Visits: 27,934
Just remember that enforcing referential constraints doesn't just ensure good, clean data. You also get performance enhancements when the query optimizer knows that foreign key constraints are in place and enforced. The clean data argument alone should be enough to win this argument, but it doesn't hurt to also know about the performance enhancements.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1545831
Posted Friday, February 28, 2014 3:41 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 10:38 AM
Points: 260, Visits: 599
Unless management "sees the light" and fully backs you in bringing order into chaos you're going to lose. It works and they haven't had problems they're willing to address.

Try for incremental changes such as formal review of new databases/tables that require the DBA team sign off on them. That might be enough of a start to move things along.

If management isn't going to actively and consistently support you then don't give yourself an ulcer. Realize you're going to be living with that environment going forward. Then decide if you want to work there.
Post #1546599
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse