SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

Using Keys 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, see “Creating an SQL/MX Database” (page 73), 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, see “Creating an SQL/MX Database” (page 73), 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, see “Creating an SQL/MX Database” (page 73), 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 columns stored in ascending order and the highest values for columns
stored in descending order. Hash partitions have a partitioning key that translates into a hash
value. All rows that translate into the same value are assigned to the same hash partition. All these
column values are referred to as the partitioning key.
For more information, see “Creating and Using a Partitioning Key” (page 83), “Creating Indexes
for SQL/MX Tables” (page 96), and the SQL/MX Reference Manual.
Foreign Key
The foreign key is the column or set of columns specified in the FOREIGN KEY clause of ALTER
TABLE or CREATE TABLE, immediately following the FOREIGN KEY keywords. The foreign key
columns can contain only values that match those in the column or set of columns specified in the
REFERENCE clause of ALTER TABLE or CREATE TABLE.
The two foreign key columns must have the same characteristics (data type, length, scale, precision),
and there must be a UNIQUE or PRIMARY KEY constraint on the column or set of columns specified
in the REFERENCE clause.
Using Keys in SQL/MX Tables and Indexes 31