This editorial was originally published on Feb 19, 2006. We are reprinting pieces this week as Steve is traveling in the UK.

There is a great debate going on in the forums about using PKs/FKs to enforce RI, in other words, declarative referential integrity (DRI) as opposed to using code logic to ensure that proper relationships are maintained in a schema. The opening post is a DBA stunned that developers in his new company do not use DRI. The reason given is performance is degraded, and that's one that I can buy. There is additional overhead for checks with DRI, but it's minimal and actually David Poole has a great article about this topic coming soon.

It's interesting that one of the questions is about how many 3rd party applications do not use DRI in their databases. Who knows why, though my suspicion is that they can easily "fix" issues with the applications with backend updates rather than maintaining good DB design practices. Or that they can easily alter the application to meet changing needs at various customers' sites.

I think it's mostly the latter reason. That and laziness. I think the vast majority of developers are lazy by nature and dealing with DRI constraints when building an application is a pain. It's a pain for me, but I still think it's a good idea. Just like stored procedures add some overhead, so does DRI and many developers I have seen, both building shrink wrap and corporate software, don't want to deal with the overhead.

Personally I like having DRI implemented for the same reason that developers use objects, libraries, functions, and other consolidation techniques. It centralizes the "rules" about your application and ensures that they are always used. I agree there is overhead, but it's a minimal amount and if you are seeing this on most servers then you are probably underpowered.

My big concern is that often multiple applications or even multiple modules of a single application often need the same business rules: like no orders unless we have a valid customer number. If you depend on the application to enforce this, then you are gambling that every developer will do it correctly and the same way. In small, tightly controlled and managed environments, this works great. But as you grow your development teams, then it is easy for someone to forget to implement some RI rule or implement it differently than others.

And those bugs are hard to find.

I don't think that every application needs DRI setup in it, but if you choose to not implement it, be sure you understand the consequences of your actions. And your boss does as well.