SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-139
CREATE CONSTRAINT Statement
CREATE CONSTRAINT Statement
CREATE CONSTRAINT is a DDL statement that defines a constraint for a table. When
a constraint is in effect, all rows in the table, either directly or through a view, must
satisfy the constraint.
constraint
is the name of the constraint. constraint must be an SQL identifier that is
unique for the associated table. If SMF is installed on your node, the name of the
associated table must be either a virtual or direct name.
ON table
specifies the table associated with the constraint (or an equivalent DEFINE).
If table is a partition, the constraint applies to the entire table to which the
partition belongs. To create a constraint that applies only to a specific partition,
include the range of key values as part of the CHECK clause criteria. For example,
a constraint on a partition of the PARTLOC table in the sample database could
include this clause:
CHECK LOC_CODE >= "G00" AND LOC_CODE < "P00"
CHECK condition
is a search condition that specifies the conditions of the constraint and that is
satisfied by all existing rows of table. The search condition must follow these
rules:

The text of the condition must have fewer than 3,000 bytes.

The combined search conditions of all constraints associated with a table must
have fewer than 31,000 bytes.

The search condition cannot include a function other than UPSHIFT, and
cannot include a subquery, a host variable, or a system-created SYSKEY
column.

For any row of table, the search condition must be resolved by looking only
at that row.
DEFERRED
is an option to delay the validation of the constraint against all the existing rows in
the table. The constraint is applied immediately to the table to validate the new
inserts or updates. The locks on the table are released after the constraint is
applied. Then a browse-mode validation of the existing rows is completed. This
CREATE CONSTRAINT constraint ON table
CHECK condition [DEFERRED]