SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

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 2048 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, 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 you do not
specify a Primary key, a SYSKEY column is added. NonStop SQL/MX generates a SYSKEY column
and appends that to the clustering key.
CREATE TABLE SALES.ODETAIL
( ORDERITEM NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
ORDERNUM NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
PARTNUM NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
UNIT_PRICE NUMERIC (8,2) UNSIGNED NO DEFAULT NOT NULL,
QTY_ORDERED NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL )
STORE BY (ordernum, partnum);
Using the SYSKEY
A SYSKEY (system-defined key) is a column-defined key by NonStop SQL/MX rather than by the
user. Tables in key-sequenced files with no user-defined clustering keys have their clustering keys
defined by NonStop SQL/MX and are stored in a column named SYSKEY. The SYSKEY column’s
data type is LARGEINT SIGNED. If a table has a user-defined, non-unique clustering key, the
SYSKEY is appended to the clustering key to ensure its uniqueness.
When you insert a record in an SQL/MX table with a SYSKEY column, the key-sequenced file
system automatically generates a value for the SYSKEY column. You cannot supply the value.
For more information, see the SQL/MX Reference Manual.
82 Creating an SQL/MX Database