Oracle7 Server Application Developer's Guide

Using CHECK Integrity Constraints

Use CHECK constraints when you need to enforce integrity rules that can be evaluated based on logical expressions. Never use CHECK constraints when any of the other types of integrity constraints can provide the necessary checking (see the section "CHECK and NOT NULL Integrity Constraints"

). Examples of appropriate CHECK constraints include the following:

a CHECK constraint on the SAL column of the EMP table so that no salary value is greater than 10000

a CHECK constraint on the LOC column of the DEPT table so that only the locations "BOSTON", "NEW YORK", and "DALLAS" are allowed

a CHECK constraint on the SAL and COMM columns to compare the SAL and COMM values of a row and prevent the COMM value from being greater than the SAL value

Restrictions on CHECK Constraints

A CHECK integrity constraint requires that a condition be true or unknown for every row of the table. If a statement causes the condition to evaluate to false, the statement is rolled back. The condition of a CHECK constraint has the following limitations:

The condition must be a Boolean expression that can be evaluated using the values in the row being inserted or updated.

The condition cannot contain subqueries or sequences.

The condition cannot include the SYSDATE, UID, USER, or USERENV SQL functions.

The condition cannot contain the pseudocolumns LEVEL, PRIOR, or ROWNUM; see the Oracle7 Server SQL Reference manual for an explanation of these pseudocolumns.

The condition cannot contain a user-defined SQL function.

Designing CHECK Constraints

When using CHECK constraints, consider the ANSI/ISO standard, which states that a CHECK constraint is violated only if the condition evaluates to false; true and unknown values do not violate a check condition. Therefore, make sure that a CHECK constraint that you define actually enforces the rule you need enforced.

For example, consider the following CHECK constraint:

CHECK (sal > 0 OR comm >= 0)

At first glance, this rule may be interpreted as "do not allow a row in the EMP table unless the employee's salary is greater than zero or the employee's commission is greater than or equal to zero." However, note that if a row is inserted with a null salary and a negative commission, the row does not violate the CHECK constraint because the entire check condition is evaluated as unknown. In this particular case, you can account for such violations by placing NOT NULL integrity constraints on both the SAL and COMM columns.

Note: If you are not sure when unknown values result in NULL conditions, review the truth tables for the logical operators AND and OR in the Oracle7 Server SQL Reference manual.

Multiple CHECK Constraints

A single column can have multiple CHECK constraints that reference the column in its definition. There is no limit to the number of CHECK constraints that can be defined that reference a column.

CHECK and NOT NULL Integrity Constraints

According to the ANSI/ISO standard, a NOT NULL integrity constraint is an example of a CHECK integrity constraint, where the condition is

CHECK (column_name IS NOT NULL)

Therefore, NOT NULL integrity constraints for a single column can, in practice, be written in two forms: using the NOT NULL constraint or a CHECK constraint. For ease of use, you should always choose to define NOT NULL integrity constraints instead of CHECK constraints with the "IS NOT NULL" condition.

In the case where a composite key can allow only all nulls or all values, you must use a CHECK integrity constraint. For example, the following expression of a CHECK integrity constraint allows a key value in the composite key made up of columns C1 and C2 to contain either all nulls or all values:

CHECK ((c1 IS NULL AND c2 IS NULL) OR 
      (c1 IS NOT NULL AND c2 IS NOT NULL))