up vote 8 down vote favorite
1

Why are constraint applied in Database? Will it not be more flexible to be in code?
I'm reading a beginners book on implementing databases, So this is a beginner's question!

Let's say I design a database model, Which has this entity model:

Person, Has subtypes: Employee , Student
Student's subtypes: Graduate, Undergraduate
Employee's subtypes: Teacher, Administrator

A registered person on system can be only an Student, Or Employee, Person entity requires uniqueness of social number (which every person has only one ofcourse).

If one day the college decides that teachers (an Employee subtype) can also be student, Taking courses in their free time (It's very improbable but I cant think of anything else right now), It's much harder to change database design which could have thousands of entries, Rather than just changing the logic in code that didn't allow a person be registered both as an student and an employee.

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

share|improve this question
Mostly we care about business rules being enforced and what is the best way for that. – ypercube Apr 12 at 18:43
1  
You're actually presenting a very bad example of what constraints are used for, since the flexibility of your entities, and the expandability of the database, are mostly defined by normalization. Having said that, constraints are the final safeguard against any corrupt data ever getting into the database, even if the application is bugged, even if a new application is developed, even if an external API is added, even if someone edits the DB directly. Constraints guard the database, on top of that the business logic will also have to do its own things before trying to access the DB. – Niels 2 days ago
2  
Actually, as a graduate student I'm considered both a Student, Employee, and a Teacher. So your example isn't really improbable. – Winston Ewert 2 days ago

7 Answers

up vote 15 down vote accepted

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?

share|improve this answer
2  
+1 sometimes the best answers are those that have more questions. – JYelton Apr 12 at 17:46
So according to this answer, The rule <a person can only exist in Student's sub-type table or only in Employees sub-type table> should be applied in code, And Database has <The Student/Employee sub-type must be a valid person> constraint. Am I right? (It was book's example). thanks. – loolooyyyy Apr 12 at 19:39
@loolooyyyy: Yes, I think that's correct. If the database enforces the first rule (that a person can only be a student or an employee) then the situation you described (in which an employee wants to register for a class) is impossible because: the person cannot be both, and it's not even possible to create a second "person" record because they can't share Social Security Numbers which are presumably issued from a third party (such as the government). Of course, this overly restrictive data model might work for some cases... – FrustratedWithFormsDesigner Apr 12 at 20:03
1  
@loolooyyyy: Another way to use the original data model and still let teachers be students might be to have another table called teachers_as_students which is another subtype of Students and has a new foreign key refering to Teachers, and a system-generated primary key, instead of a Social Security Number. This way, a "student" actually is an alias for a teacher so the teacher can still register to take a class. It's hard to say for sure how well this would work without seeing the whole data model. – FrustratedWithFormsDesigner Apr 12 at 20:09
up vote 9 down vote

Because:

  1. I want all the data in the database to be subject to the same constraints, not just the new data to be subject to the constraints in the version of the code that's running today.
  2. I want declarative constraints, not programmatic constraints.
  3. Data in the database often outlives the code that's written to interact with it today. And that data -- not the code -- is the organisation's asset.
  4. My code becomes much simpler when I know that all data is subject to rigorous constraints. I no longer have to consider special cases which I know that the database guarantees to be impossible.

Just some reasons that are important to me.

share|improve this answer
up vote 5 down vote

Most referential integrity constraints that are implemented outside of the database can be defeated, so if you want your data to have guaranteed integrity at all times then you have to apply constraints in the database. Full stop, that's it.

Typically application-level constraints are defeated though the database read consistency mechanism, by which sessions cannot view other sessions' data until it is committed.

Therefore two session can intend to insert the same value into a column that is intended to be unique, can both check at the same time that the value does not already exist, insert the values, and commit. A unique constraint implemented in the database would not let this happen.

This is not unknown to application language designers, by the way. Read section 3.10 uniqueness in the Ruby on Rails Guides: Active Record Validations and Callbacks

This helper validates that the attribute’s value is unique right before the object gets saved. It does not create a uniqueness constraint in the database, so it may happen that two different database connections create two records with the same value for a column that you intend to be unique. To avoid that, you must create a unique index in your database.

share|improve this answer
up vote 3 down vote

The data will likely long outlive the application code. If the rule is critical to the data being useful over time (like foreign key constraints that help keep the integrity of the data), it must be in the database. Otherwise you risk losing the constraint in a new application that hits the database. Not only do multiple applications hit databases (Including some that might not realize there is an important data rule) but some of them such as data imports or reporting applications may not be able to use the data layer set up in the main data entry application. An frankly, the chances of there being a bug in the constraint are much higher in application code in my experience.

In my personal opinion (based on over 30 years of dealing with data and experience with hundreds of different databases used for many different purposes) anyone who doesn't put the contraints in the database where they belong will eventually have poor data. Sometimes bad data to the point of being unusable. This is especially true where you have financial/regulatory data that needs to meet certain criteria for auditing.

share|improve this answer
up vote 2 down vote

  1. Database can check constraints effectively. Better than code.

  2. Integrity constraints help database to find effective execution plan

share|improve this answer
up vote 1 down vote

Personally, I think it's easier to create and alter constraints than it is to create triggers, for instance, which would be one way to enforce your business rule using source code.

Also triggers are less likely to be portable, as they're usually written in vendor specific languages, such as PL/SQL.

But if constraints do not meet your needs, you can always use triggers to enforce your business rules.

share|improve this answer
Also triggers do not guarantee integrity, due to read consistency issues. – David Aldridge Apr 12 at 18:37
up vote 1 down vote

In addition to the other comments...

If/when you have a database where any given table can be updated by one or more applications or code paths then placing the appropriate constraints in the database means that your applications won't be duplicating the "same" constraint code. This benefits you by simplifying maintenance (reducing the number of places to change if/when there is a data model change) and ensures that the constraints are consistently applied regardless of the application updating the data.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.