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.
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 localCHECK
constraint. A column based on a domain can add additionalCHECK
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 < MAX_SALARY));