Defining a CHECK Constraint

Go Up to Creating Tables (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. Use CHECK constraints to enforce a condition that must be true before an insert or an update to a column or group of columns is allowed. The search condition verifies whether the value entered falls within a certain permissible range, or matches it to one value in a list of values. The search condition can also compare the value entered with data values in other columns.

Note:
A CHECK constraint guarantees data integrity only when the values being verified are in the same row that is being inserted and deleted. If you try to compare values in different rows of the same table or in different tables, another user could later modify those values, thus invalidating the original CHECK constraint that was applied at insertion time.

In the following example, the CHECK constraint is guaranteed to be satisfied:

CHECK (VALUE (COL_1 > COL_2));
INSERT INTO TABLE_1 (COL_1, COL_2) VALUES (5,6);

The syntax for creating a CHECK constraint is:

CHECK (<search condition>);
<search_condition> =
 <val> <operator> {<val> | (<select_one>)}

 | <val> [NOT] BETWEEN <val> AND <val>
 | <val> [NOT] LIKE <val> [ESCAPE <val>]

 | <val> [NOT] IN (<val> [, <val> ...] | <select_list>)

 | <val> IS [NOT] NULL
 | <val>
{[NOT] {= | < | >} | >= | <=}
 {ALL | SOME | ANY} (<select_list>)

 | EXISTS (<select_expr>)

 | SINGULAR (<select_expr>)

 | <val> [NOT] CONTAINING <val>
 | <val> [NOT] STARTING [WITH] <val>
 | (<search_condition>)
 | NOT <search_condition>

 | <search_condition> OR <search_condition>
 | <search_condition> AND <search_condition>
</pre>

When creating <source enclose="none" lang="sql">CHECK

constraints, the following restrictions apply:

A CHECK constraint cannot reference a domain.
A column can have only one CHECK constraint.
On a domain-based column, you cannot override a CHECK constraint imposed by the domain with a local CHECK constraint. A column based on a domain can add additional CHECK constraints to the local column definition.

In the next example, a CHECK constraint is placed on the SALARY domain. VALUE is a placeholder for the name of a column that will eventually be based on the domain.

CREATE DOMAIN BUDGET
 AS NUMERIC(12,2)
 DEFAULT 0
 CHECK (VALUE > 0);

The next statement illustrates PRIMARY KEY, FOREIGN KEY, CHECK, and the referential integrity constraints ON UPDATE and ON DELETE. The PRIMARY KEY constraint is based on three columns, so it is a table-level constraint. The FOREIGN KEY column (JOB_COUNTRY) references the PRIMARY KEY column (COUNTRY) in the table, COUNTRY. When the primary key changes, the ON UPDATE and ON DELETE clauses guarantee that the foreign key column will reflect the changes. This example also illustrates using domains (JOBCODE, JOBGRADE, COUNTRYNAME, SALARY) and a CHECK constraint to define columns:

CREATE TABLE JOB
(JOB_CODE JOBCODE NOT NULL,
JOB_GRADE JOBGRADE NOT NULL,
JOB_COUNTRY COUNTRYNAME NOT NULL,
JOB_TITLE VARCHAR(25) NOT NULL,
MIN_SALARY SALARY NOT NULL,
MAX_SALARY SALARY NOT NULL,
JOB_REQUIREMENT BLOB(400,1),
LANGUAGE_REQ VARCHAR(15) [5],
PRIMARY KEY (JOB_CODE, JOB_GRADE, JOB_COUNTRY),
FOREIGN KEY (JOB_COUNTRY) REFERENCES COUNTRY (COUNTRY)
ON UPDATE CASCADE
ON DELETE CASCADE,
CHECK (MIN_SALARY &lt; MAX_SALARY));

Advance To:

Using the EXTERNAL FILE Option