SQL/MX 3.1 Installation and Management Guide (H06.23+, J06.12+)

Creating an SQL/MX Database
HP NonStop SQL/MX Release 3.1 Installation and Management Guide—663852-001
7-13
Creating and Using Keys
multiple occurrences of other types of constraints. For more information about
constraints, see the SQL/MX Reference Manual.
Example for Creating an SQL/MX Table With a User-Defined
Primary Key
This example creates a single-partition SQL/MX table with a user-defined primary key:
CREATE TABLE CAT1.SCHEMA1.ORDERS
(ORDERNUM DECIMAL (6) UNSIGNED NO DEFAULT NOT NULL,
ORDER_DATE DATE NO DEFAULT NOT NULL,
DELIV_DATE DATE NO DEFAULT NOT NULL,
SALESREP DECIMAL (4) UNSIGNED,
CUSTNUM DECIMAL (4) UNSIGNED NO DEFAULT NOT NULL,
PRIMARY KEY (ORDERNUM))
ATTRIBUTES EXTENT (100,100)
MAXEXTENTS 24;
Creating and Using a Clustering Key
The clustering key consists of one or more columns (fields) whose values uniquely
identify the rows of a table and determine the order in which the rows are stored and
retrieved by the DP2 disk process. The combined value of all clustering key columns
for a given row acts as the logical row identifier (row-ID) for that row, and so must be
unique for the table.
When you create an SQL/MX table, you specify the clustering key column or columns
that the DP2 process uses to access table records.
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.