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

SQL/MX Statements
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
2-103
Syntax Description of CREATE TABLE
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.

You do not specify a PRIMARY KEY constraint within the CREATE TABLE
statement.

The PRIMARY KEY defined in the CREATE TABLE statement is droppable.
In any of these cases, NonStop SQL/MX implements the PRIMARY KEY as a
separate unique index.
If an explicit STORE BY clause is used, 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.
Table 2-1
lists the maximum key size with respect to the block size.