Why CHECK Constraints Matter

Well-written applications validate their data. No one disputes that. The debate has always been: Where should the validation logic go?

David Heinemeier Hansson, the inventor of Ruby on Rails, strongly believes that validation and other business logic belong in an ORM layer. I don’t think Hansson would fault anyone for putting check constraints in the database. He’d just think you were wasting your time, and violating one of the first principles of Rails development: Don't Repeat Yourself. In order to take advantage of Rails’ built-in validation machinery, you’ll have to repeat your constraints in the ORM anyway. So why bother putting them in the database at all?

In the case of most Rails applications, I can’t argue with him. The software behind this blog follows exactly that approach and doesn’t suffer. However, this blog is typical of a class of applications in which the database and the application are very tightly bound. They form a single, inseparable unit and the database is not shared.

This arrangement is not typical in the corporate world. There, databases are shared resources accessed by multiple applications, often with a mixture of data access technologies. The mixture arises because databases tend to outlast the technologies that are used to access them. SQL Servers in a Microsoft shop (for instance) may get upgraded, but ODBC, RDO, ADO, and ADO.NET have come (and in some cases gone), not to mention the vast array of DALs and ORMs built atop them.

The database is where the buck stops, and in a shared environment it’s the one constant you can count on. ORMs and DALs will come and go, but if you throw the database out, only then have you truly hit the reset button. This is why at a minimum your validation logic belongs in the database in the form of CHECK constraints.

You will almost certainly have to repeat yourself in your DAL or ORM. I see no responsible way around this. But to bet the farm on a chunk of middle-tier code that may not be used everywhere at all times is not a good idea.

I’m not naive enough to believe that CHECK constraints are the holy grail of data integrity. Along with great testing, talented development, and strong domain knowledge, they form one of the pillars of robust application development. (And they’ll help you build better ORMs, too!)

For the record, I’m not an opponent of ORMs. I think they can serve a very useful purpose. Many ORMs have powerful facilities to interact with the user interface and present the opportunity to fix a problem before it gets written to the database. However, no matter how sophisticated the business rules are in the ORM, the database should be the final arbiter of its own data integrity.

Note: This article was originally hosted on my own server in blogging software I'd written myself in RoR.