SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-8
Constraints
Constraints
An SQL/MX constraint is an object that protects the integrity of data in a table by
specifying a condition that all the values in a particular column or set of columns of the
table must satisfy.
NonStop SQL/MX enforces these constraints on SQL/MP and SQL/MX tables:
Creating, Adding, and Dropping Constraints on SQL/MX Tables
To create constraints on an SQL/MX table when you create the table, use the CHECK,
NOT NULL, PRIMARY KEY, [FOREIGN KEY] REFERENCES, or UNIQUE clauses of
the CREATE TABLE statement.
To add or drop constraints on an existing table, use the CHECK, PRIMARY KEY,
[FOREIGN KEY] REFERENCES, or UNIQUE clauses of the ALTER TABLE statement.
You will receive an error if rows that already exist in the table violate that constraint.
You can define constraints either on a single column (column constraint) or on a set of
columns (table constraint). You can create a NOT NULL column constraint by using
CREATE TABLE and drop NOT NULL by using ALTER TABLE. All other constraints
can be added or dropped by using ALTER TABLE.
You can specify a NOT NULL or PRIMARY KEY constraint as NOT DROPPABLE at
table creation time. NonStop SQL/MX implements these constraints more efficiently if
they are specified as NOT DROPPABLE. For performance reasons, all NOT NULL
NOT DROPPABLE constraints for a table are replaced by a single CHECK constraint
that enforces the entire set.
For more information on SQL/MX commands, see CREATE TABLE Statement on
page 2-74 and ALTER TABLE Statement on page 2-10.
CHECK Column or table constraint specifying a condition must be satisfied for
each row in the table. For SQL/MX tables, check constraints cannot
contain non-ISO88591 string literals.
NOT NULL Column constraint specifying the column cannot contain nulls.
PRIMARY KEY Column or table constraint specifying the column or set of columns as
the primary key for the table.
REFERENTIAL
INTEGRITY
Column or table constraint specifying a referential constraint: a
column or set of columns in the table can contain only values
matching those in a column or set of columns in the referenced table.
This type of constraint is also called a references column constraint.
(SQL/MX tables only.)
UNIQUE Column or table constraint specifying the column or set of columns
cannot contain more than one occurrence of the same nonnull value
or set of values. (SQL/MX tables only.)