SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-79
Syntax Description of CREATE TABLE
PRIMARY KEY constraint is permanent. A PRIMARY KEY constraint is
implemented more efficiently if the constraint is permanent. A SYSKEY is not
generated for a table that has a NOT DROPPABLE PRIMARY KEY.
For both a DROPPABLE and NOT DROPPABLE PRIMARY KEY, the maximum
combined length of the columns is 255 bytes.
When a UNIQUE or PRIMARY KEY constraint is created on a table, all the
constraint columns must have a NOT NULL clause in the CREATE TABLE
statement.
If the PRIMARY KEY constraint does not include the [NOT] DROPPABLE clause
and the STORE BY PRIMARY KEY clause does not appear in the table definition,
the value of the PRIMARY_KEY_CONSTRAINT_DROPPABLE_OPTION attribute
in the SYSTEM_DEFAULTS table is the default value. If that attribute does not
exist in the SYSTEM_DEFAULTS table, NOT DROPPABLE is used. Use the
SHOWDDL statement to display the default that was used. If the STORE BY
PRIMARY KEY clause appears in the table definition, the PRIMARY KEY
constraint is NOT DROPPABLE regardless of the value of the attribute.
If the PRIMARY KEY constraint does not include the [NOT] DROPPABLE clause
and the STORE BY PRIMARY KEY clause appears in the table definition, and you
make your PRIMARY KEY droppable, NonStop SQL/MX reports an error.
When possible, NonStop SQL/MX uses the primary key as the clustering key of the
table in order to avoid creating a separate, unique index to implement the primary
key constraint.
NonStop SQL/MX cannot implement the primary key as the clustering key if any of
the following are true:
•
You enter an explicit STORE BY clause, specifying a different set of columns
than those specified for the primary key
•
You do not specify a PRIMARY KEY constraint
•
The PRIMARY KEY is droppable
In any of these cases, NonStop SQL/MX implements the PRIMARY KEY as a
separate unique index.
In the scenario described by the first bullet, NonStop SQL/MX does not allow the
primary key constraint to have the NOT DROPPABLE clause. A PRIMARY KEY
which is implemented by a separate unique index is always droppable.
CHECK (condition)
is a constraint that specifies a condition that must be satisfied for each row in the
table. See Search Condition
on page 6-102.
NonStop SQL/MX checks the condition whenever an operation occurs that might
affect its value. The operation is allowed if the predicate in the condition evaluates
to TRUE or null but prohibited if the predicate evaluates to FALSE.