There are many good reasons for building your data integrity logic into constraints. When such business rules become part of your DDL, they can never be circumvented. Any change to a rule can be enforced by a single update to a constraint in the database. It is a simple and safe architecture for managing your data integrity. But is it always that simple in practice?

It is much better to put this logic in the database than to have similar logic spread scattergun throughout the many tiers and applications that access the database. With the latter approach, one then faces the task of ensuring that the rules are consistent in all places, and that no process can bypass the checks.

However, even with all your integrity logic safely corralled into the database DDL, things can still get complicated, and difficult to track, pretty quickly. Most DBAs will employ the full range of simple CHECK, DEFAULT, UNIQUE and KEY constraints that they have available. They may also have a few more complex rules to enforce that require UDF-based constraints. Of course, there are some checks that you can’t realistically do in constraints, so you’ll have some triggers thrown into the mix as well. In addition, you may have some “cascading” Referential Integrity requirements (such as “cyclic cascades“) that are not supported in SQL Server, so you’ll implement these rules using stored procedures.

At this point your logic is beginning to look a little less neat. Although it is all within the database, it can become very easy to forget or miss something when the business rules change and need to be updated. At least, it will take some time to locate all the places where changes need to be made.

If it is not possible to satisfy all integrity rules using constraints alone, then should one, instead, implement them all using triggers, so that all the rules really are in one place and can be maintained easily? This wouldn’t be a free lunch. The cost-based optimizer uses the information it finds in constraints to optimize query execution. For example, if a query being optimised uses the LOWER function, and you already have a constraint in place on the column that restricts all entries to lowercase, then the optimizer can, if conditions are right, ignore the function altogether and arrive at a faster execution plan using just the constraint.

How much performance are you actually missing out on? After all, the optimizer won’t always use constraints, if they are not trusted because they were disabled at one point, or if they use functions. Does it outweigh the relative advantage of having a single, consistent “layer” of triggers, for example, to implement your rules?

What would you do? What is your overall strategy towards implementing, and easing the management of, business rules in the database?

Cheers,

Tony.