SQL/MP Installation and Management Guide

Understanding and Planning Database Tables
HP NonStop SQL/MP Installation and Management Guide—523353-004
3-3
Primary Keys
a table, a column cannot contain the null value; the column is implicitly defined as NOT
NULL.
User-defined primary keys facilitate generic locking as explained under Using Generic
Locks on page 14-21.
System-Defined Primary Key
A system-defined primary key consists of a column named SYSKEY, generated and
maintained internally by SQL. For each row (record), the SYSKEY column contains a
unique system-generated value. The SYSKEY value alone serves as the primary key
for a key-sequenced table when no primary key is specified by the PRIMARY KEY
clause and no clustering key is specified by the CLUSTERING KEY clause.
When a table is stored on the basis of SYSKEY values, the rows are not stored on the
disk in an order that is particularly useful, except for queries that scan rows in the order
of their entry. Consequently, queries often result in scans of the entire table.
Alternatively, when an index is used, queries that access a range of rows are not
processed efficiently because they require multiple random accesses to the table. To
improve efficiency in the absence of a primary key, you can physically order and
access the table by using a clustering key (described in Clustering Key Combined With
System-Defined Key).
The SYSKEY column has the data type LARGEINT SIGNED. The value is a unique
eight-byte number generated by the system on the basis of timestamps. SYSKEY is
physically created as the first column of the table. The description of the table in the
catalog reflects the presence of the SYSKEY column.
Inserts into the file cannot specify a value for SYSKEY, and SYSKEY values cannot be
updated.
A key-sequenced table with a system-defined primary key cannot have partitions.
Clustering Key Combined With System-Defined Key
A combination key consists of a user-defined clustering key, with columns specified in
the CLUSTERING KEY clause of the CREATE TABLE statement and a system-defined
SYSKEY column. When you use the CLUSTERING KEY clause, SQL/MP appends the
ascending system-defined SYSKEY column to the last column of the potentially
nonunique clustering key to make a unique primary key. The clustering key is always a
subset of the primary key.
Although SYSKEY is appended to the logical clustering key, the SYSKEY column is
physically created as the first column of the table. The SYSKEY column has the data
type LARGEINT.
Clustering keys apply to key-sequenced tables only.
A clustering key is useful for cases in which a table does not naturally contain a unique
SQL primary key, but ordering and scanning by SYSKEY alone or access through an
index is not desirable. Within the file, records can then be physically ordered by the