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-12
Creating and Using Keys
•
Partitioning key. See Creating and Using a Partitioning Key on page 7-14 and
Creating Indexes for SQL/MX Tables on page 7-31.
•
Partially decoupling the clustering and partitioning keys. See Partially Decoupling
the Clustering Key and the Partitioning Key on page 7-15.
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 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.