Is it good to use check constraints for business rules

Currently we are using check constraints for business rules implementation, but I want to know if we should implement business rules in SQL or in the business logic layer (C#). I have searched on the net开发者_开发百科 and found that check constraints are good to use.

Please let me know if someone knows more detailed information about it. One more thing is that the data can be pumped into my database using a mobile application and also using a web application.

YES it is good!

You should really always check your business rules both in your app code (in the business layer), but if ever possible also in your database.

Why? Imagine someone manages to submit some data to your database without using your app - if you have your checks only in the app, those checks are not being applied.

If you have your checks on the database as well, you can make sure the data in the database conforms to at least those simple checks that can be formulated in SQL CHECK CONSTRAINTS.

Definitely use those! You need to try and keep your data quality as high as possible - adding referential integrity, check constraints and unique constraints and so forth on the database helps you do that.

Do not rely on your app alone!

Yes, check constraints are a valid tool for business rules.

But are you sure you need to use check constraints, or use a supporting table with a foreign key relationship? If you find yourself defining similar check constraints in various places - the answer is yes, this should definitely be a supporting table.

Data integrity is key; there's not much value to a system that will allow a person to store something that is not per business rules if the application is circumvented. It also makes life a lot easier if the logic is in the database for situations where the original app is in C# and the higher-ups decided the market needs a Java/Ruby/Python/etc version.

You should definitely use CHECK constraints where possible, but I also wouldn't over do it. If there is no possibility of getting data into your database without using your applications, you can be safe with minimal CHECK constraints and heavy business validation.

It can be fairly difficult to define strict business rules in SQL. Stick to data validation in the database, and actual business rules in your application.

Also, try to arrange your schema in such a way that makes it difficult to enter bad data with foreign keys and the like.

As more "intelligent" is your database, more secure will be the integrity of the data it contains. So, yes, I think this is good and important to implement it.

This puts in lots of advantages: you can ensure that your data will be secure if there are more than one application modifying the data (ex: C# app + Web app + Mobile app ...) and it allow you to make less work in those "secundary" applications. If the database do all the work, apps are only a frontend for the database.

It will be easier in the future to migrate the applications, but will be more dificult to migrate the database. This is an important decision.

Depends on the constraints

1. It depends on the constraints
2. You should also try to avoid (if possible) having the same constraint checked in 2 places - this would imply there is duplicated code in your system, leading to unnecesary complexity.

There are some constraints that can and should be applied in the database, for example foreign key constraints and uniqueness. The database will be able to apply these quickly and efficently.

Other more complex "business" constraints are better applied in the business logic layer. Examples of these might be "customer must have a validated email address before allowing a purchase". These would be complicated and onerous to apply in the database - you'd run the risk of coding your system in SQL which is A Bad Idea.

C#. It's much easier to reuse logic in C# than SQL (in my experience) and generally maintain.