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-13
Creating and Using Keys
Create a clustering key by using one of these methods:
•
Use STORE BY key-column-list in CREATE TABLE to specify the columns
that compose the clustering key. The key columns must be specified as NOT NULL
NOT DROPPABLE and cannot have a combined length of more than 247 bytes.
•
Use STORE BY PRIMARY KEY to base the clustering key on the primary key
columns. This STORE BY option requires that a primary key first be defined in the
PRIMARY KEY constraint for CREATE TABLE and that it is NOT DROPPABLE. If
the primary key is defined as DROPPABLE, you cannot use it as the clustering key.
If you attempt to use it, NonStop SQL/MX returns an error.
•
If you omit the STORE BY clause and do not specify a PRIMARY KEY that has the
NOT DROPPABLE option, the clustering key storage order is determined by the
SYSKEY only.
Before deciding which columns to use in your clustering key, carefully consider these
guidelines:
•
Each SQL/MX table and index must have just one clustering key. Each column of a
clustering key can be in either ascending or descending order. The columns need
not be contiguous.
•
The columns of the clustering key cannot contain null values. If you do not specify
NOT NULL for a clustering key column, an error is returned.
•
After you have defined the clustering key, you cannot change the choice of which
columns compose the clustering key.
When using the STORE BY key-column-list option to define the columns of a
clustering key, consider whether the defined set of columns is the most appropriate
with respect to the actual intent of the table. If a column is used as the main access
path to a table, consider defining it as the clustering key.
For example, suppose that the column employee number from the table EMPLOYEE
is one of the clustering key columns. However, if most table access is by employee
name, you should consider defining employee name as the clustering key. The key
must include enough information to make it unique (perhaps by including employee
number as another column of the clustering key).
For more information about the clustering key, see the SQL/MX Reference Manual.
Example for Creating an SQL/MX Table Using the STORE BY
Clause
This example shows a CREATE TABLE command that uses the STORE BY clause to
assign two of the table’s columns, ORDERITEM and ORDERNUM, to the table’s
clustering key. If the specified clustering key columns do not uniquely identify a row,