CONSTRAINT Definition (constraint_definition) 

A CONSTRAINT definition (constraint_definition) defines an integrity condition that must be fulfilled by all the rows in one table.

Syntax

<constraint_definition> ::= CHECK <search_condition>
| CONSTRAINT <search_condition>
| CONSTRAINT <constraint_name> CHECK <search_condition>

Examples

SQL Tutorial, Constraints

Explanation

A CONSTRAINT definition defines an integrity condition that must be fulfilled by all the column values for the column defined by the column definition with CONSTRAINT definition.

The CONSTRAINT definition in a column is checked when a row is inserted and a column changed that occurs in the CONSTRAINT definition. If the CONSTRAINT definition is violated, the INSERT or UPDATE statement fails.

The search condition (search_condition) of the CONSTRAINT definition must not contain a subquery and must only contain column names in the form column_name.

<constraint_name>

●     No constraint name specified:
The database system assigns a constraint name that is unique for the table in question.

●     Constraint name specified:
The constraint name must be different to all other constraint names for this table.

<search_condition>

The number of columns in a search condition plays a role.

●     The search condition contains only one column name in the table:
When the table is created (CREATE TABLE statement), you can check whether an additional DEFAULT value specified as a column attribute fulfills the search condition. If it is not true, the CREATE TABLE statement fails.

●     The search condition contains more than one column name in the table:
When the table is created (CREATE TABLE statement), it is not possible to decide whether DEFAULT values of the table columns fulfill the search condition. In this case, an attempt to insert DEFAULT values in the table when an INSERT or UPDATE statement is executed may fail.

See also:

Ensuring Data Integrity