1.1

Table Constraint
This constraint allows you to specify table-level constraints, including the primary key, a unique key, or a foreign
key. Alternatively, a check constraint can be specified that is tested for each INSERT, UPDATE, and DELETE
operation.
Syntax
[ CONSTRAINT constraint-name ]
{
CHECK (
CHECK Constraint Search Condition
) |
{
PRIMARY KEY ( column-name [ , column-name ]* ) |
UNIQUE ( column-name [ , column-name ]* ) |
FOREIGN KEY ( column-name [ , column-name ]* )
REFERENCES table-name [ ( column-name [ , column-name ]* ) ]
[ ON DELETE RESTRICT ]
}
}
CHECK Constraint Search Condition
A search condition is a boolean expression that is specified in a CHECK constraint. The expression cannot be
dynamic and so cannot contain dynamic parameters, date/time functions, user functions, or sub-queries.
If a check constraint is included as part of a column-definition, a column reference can only be made to the same
column.
If a check constraint is specified as part of a table definition, column references can specify any columns that
were previously defined in the CREATE TABLE statement. When specified in a table constraint, the search
condition must be satisfied for all rows in the table. The search condition is applied to each row that is modified
on an INSERT or UPDATE at the time of the row modification. The entire statement is aborted if any check
constraint is violated.
Examples of Constraints
-- column-level primary key constraint named OUT_TRAY_PK:
CREATE TABLE SAMP.OUT_TRAY
(
SENT TIMESTAMP,
DESTINATION CHAR(8),
SUBJECT CHAR(64) NOT NULL CONSTRAINT OUT_TRAY_PK PRIMARY KEY,
NOTE_TEXT VARCHAR(3000)
);
-- the table-level primary key definition allows you to
-- include more than one columns in the primary key definition:
CREATE TABLE SAMP.SCHED
(
CLASS_CODE CHAR(7) NOT NULL,
DAY SMALLINT NOT NULL,
STARTING TIME,
ENDING TIME,
PRIMARY KEY (CLASS_CODE, DAY)
);
481
SQL Language Reference