SQL/MX 3.2 Management Manual (H06.25+, J06.14+)

Naming Constraints
When you create a constraint, either specify a name for it or let NonStop SQL/MX name it.
Constraint names are three-part logical ANSI names. Constraints have their own namespace within
a schema, so a constraint can have the same name as a table, index, or view in the schema.
However, two constraints in a schema cannot have the same name.
If you do not specify a constraint name, NonStop SQL/MX constructs an SQL identifier as the name
for the constraint and qualifies it with the catalog and schema of the table. The SQL identifier
consists of the first 20 characters of the table name concatenated with a system-generated unique
identifier. Use the SHOWDDL command to display this generated constraint name.
For more information, see the SQL/MX Reference Manual.
Constraints Implemented With Indexes
NonStop SQL/MX uses unique indexes to implement all UNIQUE constraints, including PRIMARY
KEY constraints, if the PRIMARY KEY constraint is not the clustering key. NonStop SQL/MX uses
nonunique indexes to implement the foreign key portion of all referential constraints added with
ALTER TABLE.
When you add one of these constraints, NonStop SQL/MX determines if a supporting index for
that constraint already exists. If it does not, NonStop SQL/MX automatically creates and loads a
new index—if possible, with the same name as the constraint— only if the supporting index does
not already exist.
The index NonStop SQL/MX creates to enforce constraints is nonpartitioned, uses the default extent
sizes, and is created in the same location as its parent table’s primary partition. Therefore, if the
parent table contains a large amount of data, the index might not be large enough to hold the
data or the disk might run out of space. If NonStop SQL/MX cannot create the supporting index,
the ADD CONSTRAINT operation fails.
If, after creating the constraint, you want to change the attributes of the default index, you can use
the MODIFY command to add or move partitions and the ALTER TABLE command to change the
MAXEXTENTS value.
When working with large tables, you should create the supporting index for each table before
you create the constraint. You precreate the index by issuing a CREATE [UNIQUE] INDEX on the
columns you want to specify in the constraint. UNIQUE is required for the PRIMARY KEY and
UNIQUE constraints. Then you issue the ALTER TABLE ADD CONSTRAINT statement.
When you precreate the index, you can specify the particular attributes you want for the index
(for example, index size, number and location of partitions, and index location) instead of relying
on possibly inadequate default values of an index created as a by-product of creating a table
constraint.
For more information, see “Precreating Indexes or Managing Constraint-Created Indexes”
(page 36)and the “Creating Indexes for SQL/MX Tables” (page 95).
Guidelines for Creating Constraints
• Aggregate functions and subqueries are not allowed in the search condition defining a CHECK
constraint.
• To make the best use of constraints that are implemented by indexes, create your indexes
before you create your constraints.
• Use the NOT DROPPABLE option on a NOT NULL constraint to reduce the space required for
the table. In a column that allows nulls—or that might allow nulls at a later time—extra bytes
are added in each row to store the null indicator. If you specify that the NOT NULL constraint
is NOT DROPPABLE, NonStop SQL/MX creates the table without these extra bytes.
Creating Constraints on SQL/MX Tables 99