Tuple-Based CHECK Constraints

Tuple-Based CHECK Constraints

To declare a constraint on the tuples of a single table R, when we define that table with a CREATE TABLE statement we may add to the list of attributes and key or foreign-key declarations the keyword CHECK followed by a parenthesized condition. This condition can be anything that could appear in a WHERE clause. It is interpreted as a condition about a tuple in the table R, and the attributes of R may be referred to by name in this expression. On the other hand, as for attribute-based CHECK constraints, the condition may also mention, in subqueries, other relations or other tuples of the same relation R.

The condition of a tuple-based CHECK constraint is checked every time a tuple is inserted into R and every time a tuple of R is updated, and is evaluated for the new or updated tuple. If the condition is false for that tuple, then the constraint is violated and the insertion or update statement that caused the violation is rejected. On the other hand, if the condition mentions some relation (even R itself) in a subquery, and a change to that relation causes the condition to become false for some tuple of R, the check does not restrain this change. That is, like an attribute-based CHECK, a tuple-based CHECK is invisible to other relations.

While tuple-based checks can involve some very complex conditions, it is often best to leave complex checks to SQL's "assertions," which we discus in "Schema-Level Constraints and Triggers". The reason is that, as discussed above, tuple-based checks can be violated under some conditions. On the other hand, if the tuple-based check involves only attributes of the tuple being checked and has no subqueries, then its constraint will always hold. Here is one example of a simple tuple-based CHECK constraint that involves several attributes of one tuple.

Example 1 :  Consider "Simple Table Declarations" Example 1, where we declared the schema of table Moviestar. Figure 1 repeats the CREATE TABLE statement with the addition of a primary-key declaration and one other constraint, which is one of many possible "consistency conditions" that we might wish to check. This constraint says that if the star's gender is male, then his name must not begin with 'Ms.'.

A constraint on the table Moviestar

Writing Constraints Correctly

In line (2), name is declared the primary key for the relation. Then line (6) declares a constraint. The condition of this constraint is true for every female movie star and for every star whose name does not begin with 'Ms.' . The only tuples for which it is not true are those where the gender is male and the name does begin with 'Ms.' . Those are exactly the tuples we wish to exclude from Moviestar.