Constraints on Attributes and Tuples

Constraints on Attributes and Tuples

We have seen key constraints, which compel certain attributes to have distinct values among all the tuples of a relation, and we have seen foreign-key constraints, which enforce referential integrity between attributes of two relations. Now, we shall see a third important kind of constraint: one that limits the values that may appear in components for some attributes. These constraints may be expressed as either:

I. A constraint on the attribute in the definition of its relation's schema, or

2. A constraint on a tuple as a whole. This constraint is part of the relation's schema, not associated with any of its attributes.

In this Section we shall introduce a simple type of constraint on an attribute's value: the constraint that the attribute not have a NULL value. Then we shall cover the principal form of constraints of type (1): attribute-based CHECK constraints. The second type, the tuple-based constraints, are covered in "Tuple-Based CHECK Constraints".

There are other, more general kinds of constraints that we shall meet in "Schema-Level Constraints and Triggers". These constraints can be used to restrict changes to whole relations or even several relations, as well as to constrain the value of a single attribute or tuple.

Not-Null Constraints

One simple constraint to associate with an attribute is NOT NULL. The effect is to disallow tuples in which this attribute is NULL. The constraint is declared by the keywords NOT NULL following the declaration of the attribute in a CREATE TABLE statement.

Example 1 :  Assume relation Studio required presC# not to be NULL, perhaps by changing line (4) of "Maintaining Referential Integrity" Figure 1 to:

4)     presC# INT REFERENCES MovieExec(cert#) NOT NULL

This change has several consequences. For instance:

●  We could not insert a tuple into Studio by specifying only the name and address, because the inserted tuple would have NULL in the presC# component.

●  We could not use the set-null policy in situations like line (5) of "Maintaining Referential Integrity" Figure 1, which tells the system to fix foreign-key  violations by making presC# be NULL.

Attribute-Based CHECK Constraints

More complicated constraints can be attached to an attribute declaration by the keyword CHECK, followed by a parenthesized condition that must hold for every value of this attribute. In fact, an attribute-based CHECK constraint is likely to be a simple limit on values, such as an enumeration of legal values or an arithmetic inequality. Nevertheless, in principle the condition can be anything that could follow WHERE in an SQL query. This condition may refer to the attribute being constrained, by using the name of that attribute in its expression. On the other hand, if the condition refers to any other relations or attributes of relations, then the relation must be introduced in the FROM clause of a subquery (even if the relation referred to is the one to which the checked attribute belongs).

An attribute-based CHECK constraint is checked whenever any tuple gets a new value for this attribute. The new value could be introduced by an update for the tuple, or it could be part of an inserted tuple. If the constraint is violated by the new value, then the modification is rejected. As we shall see in Example 3, the attribute-based CHECK constraint is not checked if a database modification does not change a value of the attribute with which the constraint is associated, and this limitation can result in the constraint becoming violated. First, let us consider a simple example of an attribute-based check.

Example 2 :  Assume we want to require that certificate numbers be at least six digits. We could modify line (4) of "Maintaining Referential Integrity" Figure 1, a declaration of the schema for relation

      Studio(name, address, presC#)

to be.

      4)     presC# INT REFERENCES MovieExec(cert#)
                        CHECK (presC# >= 100000)

For another example, the attribute gender of relation

  MovieStar(name, address, gender, birthdate)

was declared in "Simple Table Declarations" Figure 1 to be of data type CHAR(1) - that is, a single character. However, we really expect that the only characters that will appear there are 'F' and 'M'. The following substitute for line (4) of "Simple Table Declarations" Figure 1 enforces the rule.

  4)  gender CHAR(1) CHECK (gender IN ('F' , 'M')),

The above condition uses an explicit relation with two tuples, and says that the value of any gender component must be in this set.

It is allowed for the condition being checked to mention other attributes or tuples of the relation, or even to mention other relations, but doing so requires a subquery in the condition. As we said, the condition can be anything that could follow WHERE in a select-from-where SQL statement. On the other hand, we should be aware that the checking of the constraint is associated with the attribute in question only, not with every relation or attribute mentioned by the constraint. As a result, a complex condition can become false if some element other than the checked attribute changes.

Example 3 :
We might assume that we could simulate a referential integrity constraint by an attribute-based CHECK constraint that requires the existence of the referred-to value. The following is an erroneous attempt to simulate the requirement that the presC# value in a

      Studio(name, address, presC#)

tuple must appear in the cert# component of some

    MovieExec(name, address, cert#, netWorth)

tuple. Suppose line (4) of "Maintaining Referential Integrity" Figure 1 were replaced by

      4)     presC# INT CHECK
                 (presC# IN (SELECT cert# FROM MovieExec))

This statement is a legal attribute-based CHECK constraint, but let us consider its effect.

●  If we attempt to insert a new tuple into Studio, and that tuple has a presC# value that is not the certificate of any movie executive, then the insertion is rejected.

●  If we attempt to update the presC# component of a Studio tuple, and the new value is not the cert# of a movie executive, the update is rejected.

●  However, if we change the MovieExec relation, say by deleting the tuple for the president of a studio, this change is invisible to the above CHECK constraint. In this way, the deletion is allowed, even though the attribute-based CHECK constraint on presC# is now violated.

We shall see in "Schema-Level Constraints and Triggers" how more powerful constraint forms can correctly express this condition.