General Constraints

General Constraints
Updates to tables may be constrained by enterprise rules governing the real-world transactions
that are represented by the updates. For example, DreamHome may have a rule that
prevents a member of staff from managing more than 100 properties at the same time. The
ISO standard allows general constraints to be specified using the CHECK and UNIQUE
clauses of the CREATE and ALTER TABLE statements and the CREATE ASSERTION
statement. We have already discussed the CHECK and UNIQUE clauses earlier in this
section. The CREATE ASSERTION statement is an integrity constraint that is not directly
linked with a table definition. The format of the statement is:
CREATE ASSERTION AssertionName
CHECK (searchCondition)
This statement is very similar to the CHECK clause discussed above. However, when
a general constraint involves more than one table, it may be preferable to use an ASSERTION
rather than duplicate the check in each table or place the constraint in an arbitrary
table. For example, to define the general constraint that prevents a member of staff from
managing more than 100 properties at the same time, we could write:
CREATE ASSERTION StaffNotHandlingTooMuch
CHECK (NOT EXISTS (SELECT staffNo
FROM PropertyForRent
GROUP BY staffNo
HAVING COUNT(*) > 100))
We show how to use these integrity features in the following section when we examine the
CREATE and ALTER TABLE statements.