SQL/MX 2.x Installation and Management Guide (H06.10+, J06.03+)

Understanding and Planning SQL/MX Tables

HP NonStop SQL/MX Installation and Management Guide—544536-007

4-5

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

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 about the partitioning key, see Creating and Using a Partitioning

Key on page 7-15, Creating Indexes for SQL/MX Tables on page 7-33, 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.

The Key-Sequenced File Structure

All SQL/MX tables are key-sequenced files, which store rows (records) that contain a

clustering key. New rows are stored in sequence by clustering key value. A user

performing update operations can update or delete rows and lengthen or shorten

values in a varying-length column (VARCHAR, NCHAR VARYING) when the column is

not part of the clustering key.