NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
C-132
Considerations—CREATE CONSTRAINT
•
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, 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.
Considerations—CREATE CONSTRAINT
•
Authorization and access requirements
To create a constraint, you must be a generalized owner of the underlying table. You
must also have authority to read the table and authority to write to affected catalogs.
CREATE CONSTRAINT requires an exclusive open on table, including any
partitions. The operation fails if the table is inaccessible or if other users have the
table open.
Only one DDL statement can operate on a given SQL object (or partition of an SQL
object) at a time. An error occurs if you attempt to execute a CREATE
CONSTRAINT statement while another process is executing a DDL operation on
the same object. The specific error depends on the DDL operation involved and the
phase of the operation at which the conflict occurs. (See DDL (Data Definition
Language) Statements on page D-19 for more information.)
•
Existing constraints
When you create a constraint, SQL adds it to those constraints that already exist for
the table. The new constraint affects later INSERT and UPDATE operations; it does
not affect existing constraints.
You can determine the existing constraints for a table by querying the CONSTRNT
table of the catalog that contains the table description.
To cancel a constraint, use the DROP statement.
•
Existing cata
CREATE CONSTRAINT fails if the table contains data that violates the constraint
being created.
•
Effect on transactions
When your table is large, you might want to avoid executing CREATE
CONSTRAINT in a user-defined TMF transaction. For a large table, the CREATE
CONSTRAINT operation might run for a long time. The delay could cause TMF to
require too much log file space to perform the logging required for all users.
If no user-defined TMF transaction is in progress when CREATE CONSTRAINT
executes, SQL automatically starts several separate transactions during the
operation. None of these transactions spans the entire lengthy period during which
the table is tested for qualifying rows.