Curtis Poe takes a turn at shooting down people who think foreign keys aren’t important with a O’Reilly blog post: Misunderstanding Foreign Keys. Curtis seems to have been inspired by this Are Foreign Keys Worth Your Time? blog post. It looks like Ruby on Rails is partly to blame for this attitude by not properly supporting foreign keys. That points back to MySQL and their off again on again history for foreign keys.
It is unfortunate that there are people who think that databases without foreign keys should be the norm and not the exception.
Catching errors and problems at the application level is a good thing, by all means please do so. But don’t, ever, EVER, use that as an excuse to not have proper checks in place at the database layer. I’d liken this to the same people who feel that they don’t need to validate web data on the server side because they are doing that on the browser side with javascript. So what happens when someone comes along who has javascript turned off? So much for you data validation. The same thing is true of the database and application relationship. What happens when someone writes a new tool for accessing your database and all of you application level checks are skipped?
Very bad things, that’s what.
One reply on “Misunderstanding Foreign Keys”
I couldn’t agree more. My current company is data driven and was built on MySQL many years ago with no foreign keys, not null constraints, etc… and it really shows with orphaned records, null’s where they shouldn’t be etc…! I love Ruby on Rails and use it regularly but the posts I see about doing all the validation in the model and not worrying about creating unique indexes, foreign keys, etc… are just bad design in my opinion. One of the few voices I’ve heard in the Ruby community that seems to recognize the value of database constraints is Robby Russel!