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

Understanding and Planning SQL/MX Tables
HP NonStop SQL/MX Release 3.1 Installation and Management Guide—663852-001
4-4
Types of Keys Used in SQL/MX Tables and Indexes
Types of Keys Used in SQL/MX Tables and
Indexes
At the physical level, a key is a field or group of fields that the system can use to order
records and to identify records for processing. SQL/MX tables and indexes use the
keys described in this subsection.
Primary Key
A primary key is the column or set of columns that define a unique key for an SQL/MX
table. The primary key is a constraint and must conform to the ANSI rules for
constraints.
All columns defined for the primary key must be defined as NOT NULL. If you want to
use the primary key as the clustering key, the primary key constraint must be defined
as NOT DROPPABLE.
For more information about primary keys, see Section 7, Creating an SQL/MX
Database, and the SQL/MX Reference Manual.
Clustering Key
NonStop SQL/MX organizes records of a table or index by using a B-tree based on the
clustering key. Values of the clustering key act as logical row-IDs. The values of the set
of columns that make up the clustering key must be unique for each table row.
For more information about the clustering key, see Section 7, Creating an SQL/MX
Database, and the SQL/MX Reference Manual.
SYSKEY
A SYSKEY (or system-defined clustering key) is a clustering or storage key defined by
NonStop SQL/MX instead of the user. SQL/MX tables with no user-defined clustering
key have a clustering key defined by NonStop SQL/MX and stored in a column named
SYSKEY.
You can use the primary key as the clustering key. If you do not, NonStop SQL/MX
appends a SYSKEY to the specified clustering key column list. If you do not specify a
clustering key list, SYSKEY alone becomes the clustering key.
For more information about the SYSKEY, see Section 7, Creating an SQL/MX
Database, and the SQL/MX Reference Manual.
Partitioning Key
You specify a partitioning key through the PARTITION BY clause of the CREATE
TABLE or CREATE INDEX statement. The FIRST KEY option of the PARTITION
clause specifies the beginning of the range for a range-partitioned table or index
partition. The FIRST KEY clause specifies the lowest values in the partition for