SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-140
Considerations—CREATE CONSTRAINT
enables concurrent DML operations on the table to complete without waiting
behind the locks.
Considerations—CREATE CONSTRAINT

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. (For information, see DDL
(Data Definition Language) Statements on page D-20.)

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.

CREATE CONSTRAINT fails if the table contains data that violates the constraint
being created.

The DEFERRED option is not supported in a user-defined TMF transaction.
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.

When a CREATE CONSTRAINT with the DEFERRED option is used, the
constraint is applied on the table and a browse-mode validation of the existing
rows is performed. In this window, certain inserts or updates on the table fail if the
constraint is violated. Later, if the browse-mode validation of the existing rows does
not satisfy the constraint, it will be dropped. As a result, the inserts or updates fail
due to the nonexistent constraint.

CREATE CONSTRAINT invalidates SQL programs that use the underlying table.