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

Understanding and Planning SQL/MX Tables
HP NonStop SQL/MX Installation and Management Guide—523723-004
4-4
Types of Keys Used in SQL/MX Tables and Indexes
page 4-5 discusses the structure of key-sequenced files. An understanding of the
key-sequenced file structure can help you plan the best use of disk storage space
when sizing your SQL/MX database, implementing economical table-access methods,
and analyzing various performance trade-offs. This understanding is also essential for
anyone using the FCHECK utility to operate on physical file structures.
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.