SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-53
Using the CREATE CONSTRAINT Statement
Additional Guidelines
When defining constraints, also consider these guidelines:
•
You can use collations in the search condition defining a constraint.
•
Aggregate functions and subqueries are not allowed in the search condition
defining a constraint.
•
For any given row of a table, the constraint must be able to be resolved by
checking only that row.
•
Constraint names should be as descriptive as possible.
•
If you create a comment on a constraint, applications can use the comment text for
routines that handle errors related to that constraint.
•
Ensure that constraints on the same table are not logically in conflict. A conflict
could cause all rows to be invalid.
The CONSTRNT catalog table contains the description of constraints for tables
recorded in the catalog. You can query this table to display the constraints on a
table.
This example queries the CONSTRNT table but first sets VARCHAR_WIDTH to
255 so that 255 characters of each row of the constraint definitions are displayed
instead of 80 characters (the default width):
>> SET STYLE VARCHAR_WIDTH 255;
>> SELECT * FROM CONSTRNT
+> WHERE TABLENAME = "\SYS1.$VOL1.PERSNL.EMPLOYEE";
•
To place a constraint on a particular partition, include the partition keys as part of
the WHERE clause criteria in the search condition that defines the constraint.
•
Whenever possible, you should create constraints after creating the table but
before loading data into the table. If you create the constraint before loading the
table, the data integrity of the table is ensured, because the LOAD utility does not
put rows into a table if the rows do not conform to constraints. Depending on the
error limit specified in the ALLOWERRORS option, the load operation either fails
when encountering a row that does not conform to the constraints or loads only
rows that do conform.
•
If you are creating a constraint on an existing table, you should perform an
interactive query on the table that is a negation of the constraint. This query
identifies rows that violate the constraint. You should change or delete any
identified rows before creating the constraint. The CREATE CONSTRAINT
operation fails if rows in the table do not satisfy the constraint.
•
You should not create a constraint on a loaded table within a user-defined TMF
transaction because the transaction could overflow the TMF audit trails and cause
an error. The CREATE CONSTRAINT statement automatically initiates several
TMF transactions, as necessary, but performs tests outside a TMF transaction to