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-14
Creating and Using Keys

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 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 about SYSKEY, see the SQL/MX Reference Manual.