Misunderstanding Foreign Keys

by Curtis Poe

Imagine you're programming an application and you run across the following (pseudocode) method:

  double foo (int num) {
      return someNum/num;

Any programmer who's been programming for more than, oh, 3 days, is going to ask "what happens when you pass a zero?" What's remarkable about this is not just that the original programmer forgot to consider this case, but that many times the original programmer will have all sorts of fascinating arguments about why they deliberately ignored this case. Why is it that many serious programmers would be aghast at these arguments but somehow accept them when it comes to programming a database?


2006-05-23 21:35:39
If the original code was Java code, the JVM will throw an exception anyway if num==0. So what would be the point of adding explicit validation?
2006-05-23 23:02:26
It's only data; you'd think accuracy were important or something.
Bart van Kuik
2006-05-24 00:55:07
It's a technical issue that causes productivity problems.

The whole problem is that the database isn't really integrated in the application framework. I.e. the checks you code, should also be done in the database, the browser and the appserver. If you use PHP/PEAR's HTML_QuickForm classes, you get a check in the browser and the appserver with just one line. And that's the most I've seen until now.

2006-05-24 13:43:25
Frank, if you're dealing with Java, yes it will throw an exception and the default exception is quite likely to be fine for your needs. I still feel the explicit validation is good as I tend to adhere to the standard of "a good programmer looks both ways before crossing a one-way street".

However, even granting the Java example, what about C++? Division by zero doesn't throw an exception (though I believe it does if you're only using integers). What's worse, a tiny C program I wrote to test this returned "0.000000". That's not good.

Dave Cross
2006-05-25 01:27:58
It's not just foreign keys. There seems to be a whole generation of "database programmers" out there who don't use any kind of metadata. I put this down to two things:

1/ People working as database designers without ever having been taught any database theory. Or even reading a book on the subject.

2/ Popular database systems (and, yes, I'm looking at you MySQL 3.x) which didn't have a reasonable level of support for metadata.

Data and it's relationships should be described as much as possible in the database. That's what databases are for. They shouldn't be seen as just some static dump for data.

If you're dropping foreign keys, then what's next? I know, we'll let the application keep track of all of the data means. Let's make each database a single table. It'll have a thousand columns and all of them will be varchar(255).

2007-02-05 05:49:51
I agree with the author and the pro-FK comments. Especially by Dave Cross. I think most new "database programmers" see database as an special-featured Excel.

Always put theory (and idea) first!

2007-10-11 07:36:12
While in theory I agree, in practice this is much more problematic. If I (as a DBA) have the ability to capture the scope of a requirement and design, build, and implement that scope, my schema may contain FK's, defaults, non-nullable fields, et cetera.

Unfortunately, usually I inherit someone else's mess, designed by someone who did not understand what they were doing, or did not envision the data being used to current requirements. Ultimately, at management's impatient insistence I end up ripping out most of the constraints just to get the durn thing to work in our environment, and once it's working, nobody wants to touch it for fear of introducing more complications.

From an IT perspective, data integrity is a very important thing; however, from a business perspective, it would seem that any data (even inaccurate data) is better than no data. Go figure.

2007-10-11 07:41:55
@Jeremy: I do understand your pain. Unfortunately, developers who know a little SQL seem to assume that's all they know about databases. May I recommend picking up the book Refactoring Databases: Evolutionary Database Design as an alternative strategy?

Of course, business types are often so focused on short-term results that they don't care about long-term success. After all, they know that they (and the app) probably won't be here in a few year's time.