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


Foreign Keys are evil?


Foreign Keys are evil?

Author
Message
frankincognito404
frankincognito404
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 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?
stormsentinelcammy
stormsentinelcammy
Mr or Mrs. 500
Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)

Group: General Forum Members
Points: 507 Visits: 807
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 Smile
Luis Cazares
Luis Cazares
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40848 Visits: 19815
frankincognito404 (2/25/2014)
two developers are in charge of the database schema

That explains everything :-D
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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
EricEyster
EricEyster
Right there with Babe
Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)Right there with Babe (726 reputation)

Group: General Forum Members
Points: 726 Visits: 520
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?
RonKyle
RonKyle
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

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



Rob Taylor
Rob Taylor
Hall of Fame
Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)Hall of Fame (3.7K reputation)

Group: General Forum Members
Points: 3740 Visits: 1616
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 Smile

+1 Agree 100%

Rob
Steve Jones
Steve Jones
SSC Guru
SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)SSC Guru (142K reputation)

Group: Administrators
Points: 142928 Visits: 19424
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
My Blog: www.voiceofthedba.com
Steve-3_5_7_9
Steve-3_5_7_9
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2860 Visits: 1609
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



Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95995 Visits: 33013
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
JustMarie
JustMarie
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

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