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, ...
- A registered person on the system can only be a Student or an Employee.
- 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
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.