Should you enforce constraints at the database level as well as the application level?

Question

I've been reading the book "Enterprise Rails" by Dan Chak and it got me thinking: Do you feel that you should have data constraints at both the database level and the application level? Or do you feel similarly to opinionated frameworks like Ruby on Rails - the database is just a "dumb repository" for data, and all checks should be done in your application (I'm not trying to single out RoR here - I'm a huge fan of Rails myself, but I disagree with its approach to the database)?

Personally, I feel that you should have them both in order to make sure your database and application are well-secured. What I mean is that you should make use of not-null constraints, give your fields a length if known ( as opposed to leaving them all at nvarchar(255) ), have things such as Foreign Keys, Check Constraints and Triggers on your database, and then also enforce this through business logic rules in your application. IMO this makes your application robust through its user interface, and also secure against someone who might have direct access to the database.

The counter-argument I most often see is that it requires what amounts to duplicate logic; once at the database level, and once at the application level -- Let's say you have a check constraint to verify that a product's SKU is entered (i.e. it's length is greater than zero).

You would now need to also include validation methods in your business logic to make sure the value the user entered has a length greater than zero, and also possibly some client-side Javascript to catch the error as the user types data.

I for one do not see this as a bad thing - yes you have some duplicate logic, but the end result is the "database as fortress" mindset, since if you think about it your data is the single most important part of your application; after all, what good is your shiny new Web 2.0 application if the data can easily be corrupted and compromised?

What are your thoughts on this? Should the database be an impenetrable fortress like Fort Knox, or an open safe that's guarded by lasers? In other words, should you sacrifice some duplication of logic to ensure a secure data model, or leave everything to your application and use the database simply to store data?

Answer 1

In short : database should enforce constraints.

Why :

1. Easier. For ex. to have a constraint set on a particular data column there is only one place to set it : the column itself. The data might come from various sources but the check is put where the data is finally put to rest.
2. Integrity. Database should be responsible for the data it hosts. An inconsistent database is as good as no database.
3. Flexibility. New UI development environments come all too frequently. If database puts up its hand to say that it will take care of the constraints , the front end development and functional testing are easier.