Why Apply Constraints in Database Rather Than Code?

database-design

Why are constraint applied in Database? Will it not be more flexible to put it in the code?

I'm reading a beginners book on implementing databases, so I'm asking this as a beginner. Let's say I have designed a database, including this entity model:

 entity type    |   sub-types
----------------+--------------------------------------------
   Person       |   Employee, Student,       ...
   Student      |   Graduate, Undergraduate, ...
   Employee     |   Teacher,  Administrator, ...

Current constraints:

  1. A registered person on the system can only be a Student or an Employee.
  2. Person entity requires uniqueness of social number, which we presume every person holds only a single unique one (aka, a good enough primary key). (see #1)

Later we decide to remove the number 1: If one day the college decides that the Teacher (the Employee sub-type) can also be Student, taking courses in their free time, it's much harder to change database design which could have thousands, millions, billions, zillions of entries rather than just changing the logic in code: just the part which didn't allow a person be registered both as a student and an employee.

(It's very improbable but I can't think of anything else right now. Apparently it is possible).

Why do we care about business rules in database design rather than in code?

#1: A note 7 years later, a real life example:
I have seen a government where because of a mistake, issued SSNs were duplicated: multiple people, same SSN. Those designing the original DB definitely made that mistake of not applying this uniqueness constraint in the database. (and later a bug in the original application? multiple applications using the shared database and not agreeing where to put, check and enforce the constraint? …).
This bug will go on to live in the system and all the system developed after which rely on that original system's database, for many many years to come. Reading the answers here I learned to apply all the constraint, as many of them as possible, wisely (not blindly) in the database to represent the real physical world out there as good as I can.

Best Answer

Some constraints are best enforced in the database, and some are best enforced in the application.

Constraints that are best enforced in the database are usually there because they are fundamental to the structure of the data model, such as a foreign key contraint to ensure that a product has a valid category_id.

Constraints that are enforced in an application might not be fundamental to the data model, such as all FooBar products must be blue - but later someone might decide that FooBars can also be yellow. This is application logic that doesn't really need to be in the database, though you could create a separate colours table and the database can require that the product reference a valid entry from that table. BUT the decision that the only record in colours has the value blue would still come from somewhere outside the database.

Consider what would happen if you had no constraints in the database, and required them to all be enforced in the application. What would happen if you had more than one application that needed to work with the data? What would your data look like if the different applications decide to enforce contraints differently?

Your example shows a situation where it might have been more beneficial to have the constraint in the application rather than in the database, but perhaps there was a fundamental problem with the initial data model being too restrictive and inflexible?

Related Question