Foreign Keys are evil? [closed]

Question

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?

Answer 1

I think they are misguided or naive if what you have described is truly the developers' general approach to foreign keys. Of course it is quite possible that there are good reasons why a foreign key constraint can't or shouldn't apply to some particular attributes in any given system. Maybe that is the real reasoning behind the apparent rhetoric.

My advice. If you are stakeholder in the system in question then don't talk to the developers, talk to the development manager or whoever owns the system. Back up your case with specific examples of where lack of referential integrity is having an adverse impact or poses future risks.

If you don't have a current RI-related issue then presumably your main concern is to improve the policy or development approach for future work. Talk to the database managers, DBAs or those responsible for standards and information risks. Consider investing in some training, mentoring or consultancy for the benefit of the development team.