Check constraint benefits

A check constraint is an additional check on the value to be inserted in, or updated to a table. We say an additional check because specifying a datatype can also be seen as a check. Check constraints place data value restrictions on the contents of a column through the specification of a Boolean expression. The expression is defined in the table DDL. The formulation of a check constraint is similar to the formulation used in a SQL WHERE clause.

Some examples of check constraints:

PRICE > 0
PRICE > 0 AND PRICE <1000
PRODUCTTYPE IN ('Software','Book')

Some benefits of check constraints:

The main benefit of using check constraints is the ability to enforce rules in the database and not necessairy additional logic in the application. The check cannot be bypassed by the application.

Check constraints provide data integrity. The rule is not bypassed during ad hoc processing or dynamic SQL. When rules are enforced in the application logic instead, the rules can not be checked during ad hoc processes that can result in unwanted data or even data that can bring down the application.

Check constraints provide consistency. When a database is used by multiple different applications, the rules must be programmed in each of the applications.

The overall impact of check constraints will be to increase application development productivity.