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

Creating and Using Keys
Create and use these keys for SQL/MX tables and their indexes:
• Primary key. See “Creating and Using a Primary Key” (page 80).
• Clustering key. See “Creating and Using a Clustering Key” (page 80).
• SYSKEY. See “Using the SYSKEY” (page 81).
• Partially decoupling the clustering and partitioning keys. See “Partially Decoupling the Clustering
Key and the Partitioning Key” (page 82).
• Partitioning key. See “Creating and Using a Partitioning Key” (page 81) and “Creating Indexes
for SQL/MX Tables” (page 95).
Creating and Using a Primary Key
A primary key is the column or set of columns that defines a unique key for a table. Primary key
columns cannot contain nulls, so each one must be declared NOT NULL.
Use the PRIMARY KEY constraint clause in CREATE TABLE to specify the table columns of a primary
key. For NonStop SQL/MX, you use the primary key to:
• Specify one or more columns that uniquely define and identify each row.
• Provide an implicit method for defining the columns of the clustering key, but only if the primary
key is NOT DROPPABLE. You can use a primary key that is NOT DROPPABLE as the clustering
key. If the primary key is droppable, you cannot use it as the clustering key.
The PRIMARY KEY constraint is one of several types of constraints that you define for an SQL/MX
table. Each table or index can have only one PRIMARY KEY constraint but multiple occurrences of
other types of constraints. For more information, 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,
80 Creating an SQL/MX Database