SQL/MX 3.x Reference Manual (H06.22+, J06.11+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—640322-001
2-82
Syntax Description of CREATE TABLE
ASCENDING and DESCENDING specify the direction for entries in one column
within the key. The default is ASCENDING.
The PRIMARY KEY value in each row of the table must be unique within the table.
Columns within a PRIMARY KEY cannot contain nulls. A PRIMARY KEY defined
for a set of columns implies that the column values are unique and not null. You
can specify PRIMARY KEY only once on any CREATE TABLE statement.
DROPPABLE specifies that you can drop the PRIMARY KEY constraint with an
ALTER TABLE statement at some later time. NOT DROPPABLE specifies that the
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 a NOT DROPPABLE PRIMARY KEY, the maximum combined length of the
columns depends on the block size of the table. For a DROPPABLE PRIMARY
KEY, the maximum combined length of the columns depends on the block size of
the supporting index. For both a DROPPABLE and NOT DROPPABLE PRIMARY
KEYs, the maximum length is 2010 bytes for 4K blocks and 2048 bytes for 32K
blocks.
For a PRIMARY, CLUSTERING, or UNIQUE key, the maximum number of key
columns is 1024.
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.