SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—523723-004
7-36
Naming Constraints
If you define several NOT NULL NOT DROPPABLE constraints on a table, NonStop
SQL/MX automatically combines them into one constraint. This approach ensures that,
at run time, only one constraint check is performed. If you perform a SHOWDDL, all
constraint information is shown as one constraint. Combining constraints is
implemented only for NOT NULL NOT DROPPABLE constraints.
To drop a constraint, use an ALTER TABLE statement that specifies the name of the
constraint to be dropped.
For more information about using SQL/MX constraints in CREATE TABLE and ALTER
TABLE statements, see the SQL/MX Reference Manual.
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 about constraint names, 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.