Specifying Domain CHECK Constraints

Go Up to Creating Domains (Data Definition Guide)

You can specify a condition or requirement on a data value at the time the data is entered by applying a CHECK constraint to a column. The CHECK constraint in a domain definition sets a search condition (<dom_search_condition>) that must be true before data can be entered into columns based on the domain.

The syntax of the search condition is:

<dom_search_condition> =
 VALUE <operator> <val>

 | VALUE [NOT] BETWEEN <val> AND <val>
 | VALUE [NOT] LIKE <val> [ESCAPE <val>]
 | VALUE [NOT] IN (<val> [, <val> …])
 | VALUE IS [NOT] NULL
 | VALUE [NOT] CONTAINING <val>

 | VALUE [NOT] STARTING [WITH] <val>
 | (<dom_search_condition>)
 | NOT <dom_search_condition>
 | <dom_search_condition> OR <dom_search_condition>
 | <dom_search_condition> AND <dom_search_condition>
<operator> = {= | < | > | <= | >= | !< | !> | <> | !=}

The following restrictions apply to CHECK constraints:

• A CHECK constraint cannot reference any other domain or column name.
• A domain can have only one CHECK constraint.
• You cannot override the domain’s CHECK constraint with a local CHECK constraint. A column based on a domain can add additional CHECK constraints to the local column definition.