5

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?

Alexander Tobias Bockstaller
  • 4,657
  • 4
  • 36
  • 58
user3352063
  • 61
  • 4
  • 1
    Point (1) sounds like it could be worked around with just a little effort. Point (2) can be addressed by simply inserting the data in order which usually isn't hard to do. – Keith Feb 25 '14 at 17:00
  • 1
    This is a solicitation of opinion, and therefore, not an appropiately question for this site. – RBarryYoung Feb 25 '14 at 20:31
  • You might want to show them [this list of pros and cons](http://stackoverflow.com/a/20873843/533120). – Branko Dimitrijevic Feb 27 '14 at 02:06

2 Answers2

7

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.

nvogel
  • 24,981
  • 1
  • 44
  • 82
0

I've been designing database systems for over 30 years now. Here's my take - you're probably not going to like it. When designing closed systems, your referential integrity is probably going to be built into the application's user interface. You can only add a Product entity record where Color is required - enforced by the user interface. So why have a foreign key? I don't do Open databases where anyone can do anything - so your typical back end is going have thousands of lines of front end code protecting the integrity of the data. The bolted on foreign keys just get in the way and don't provide you with any real service. It's not like the foreign key is going to throw an exception and then you're going to go trap that exception and post a message to the user. Nobody and I mean NOBODY codes that way. All us programmers love to rush in and put all that cool stuff directly in the user interface. But like I said, I work with closed systems where the developer rules. Indexes, on the other hand - EXTREMELY IMPORTANT. Deal with that first. Foreign keys - just syntactical fluff.

Scott L Holmes
  • 111
  • 4
  • 8
    FKs save time and maintenance costs: when requirements change you change the constraint once in one place rather than every application. FKs improve query performance: the optimizer can use them to rewrite and optimize execution plans. FKs are declarative not procedural: they apply to all data all the time, not just during the times they become effective in the app. FKs support integration with other tools and processes, ETL, development and management tools, code generation and a host of other things. FKs are not syntactical fluff!! – nvogel Feb 25 '14 at 20:57
  • I would posit that you might be confusing Foreign Keys with Indexes. – Scott L Holmes Feb 26 '14 at 16:27
  • 4
    Hi Scott. I am not. Commonly it is the case that foreign key attributes are good candidates for indexing but I'm certainly not assuming that in my answer and comment. FK constraints are important and useful whether or not the FK columns happen to be indexed. – nvogel Feb 26 '14 at 16:49
  • I'm happily going to agree to disagree. Your comments are very good and I highly recommend that anyone reading them take heed. That's why I upvoted them. But I believe that mileage will vary with all of computerdom's wisdom and rules of thumb. – Scott L Holmes Feb 26 '14 at 19:07
  • 1
    Foreign keys are very useful if you understand what they are for and what they do. They will provide forced data integrity in the most common wording. In other words a value can not be inserted into a table that doesn't exist somewhere else for example, that being said you need not have a table for every conceivable value but must be used by discretion at the database level to ensure that the data is as specific as you intended otherwise yes skip the foreign keys. – Andy Braham Jan 09 '15 at 18:01
  • You are relying on humans not making mistakes here, developers not trying to use dataa that don't exists and dba not deleting data they're not supposed to. Relying on humans not mistake is a giant error. Plus in the backoffice i will indeed put validation rules but i don't want to double my code size checking that the data that is supposed to exist actually exists. – Magnesium Nov 23 '20 at 14:08