SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-52
Using the CREATE CONSTRAINT Statement
inserted. You can drop or add constraints at any time, as validity requirements for the
data change, without affecting the application programs.
Constraints created on a table ensure that any data entered into the table satisfies the
rules imposed by the constraints. To create a constraint on a table, use the CREATE
CONSTRAINT statement.
For additional information on constraints and related performance issues, see
Checking Data Integrity on page 14-23.
Using the CREATE CONSTRAINT Statement
The CREATE CONSTRAINT statement enforces these rules:
•
Constraint names are SQL identifiers that can contain at most 30 of these
characters: letters (A-Z, a-z), digits (0-9), and the underscore (_). The name must
begin with a letter. SQL/MP reserved words, listed in the SQL/MP Reference
Manual, are not allowed.
These are examples of constraint names:
VALID_EMPLOYEE_NUMBER
VALID_JOB_CODES
VALIDENDDATE
MAXIMUM_SALARY
•
Although you can specify constraint names in the CREATE CONSTRAINT
statement in either uppercase or lowercase letters, the internal format is always the
same: uppercase letters. So, the constraint names MAXIMUM_SALARY and
maximum_salary are equivalent.
•
The SYSKEY column is not allowed in the search condition defining a constraint.
•
The CREATE CONSTRAINT statement requires an exclusive open of the
underlying table, including all partitions, to ensure that no rows are inserted during
the creation of the constraint. To add a constraint on a table loaded with data, the
system verifies that all rows in the table satisfy the constraint. On a very large
table, this processing can run for an extended time. You should create a constraint
when the application is not active.
•
The CREATE CONSTRAINT statement requires an exclusive table.
•
You cannot create constraints directly on views. The constraints on underlying
tables, however, affect the dependent views.