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-4
Types of Keys Used in SQL/MX Tables and Indexes
block and is convenient for reading multiple records sequentially. However, the large
block support feature might not be useful for random access, where only one or a few
records are accessed at a time. Therefore, you must select a disk block size
depending on the type of most frequently used queries to the database. By default, the
table is created with 4096 bytes (4 KB) disk block size.
SQL/MX tables use the key-sequenced file structure. Other table and file structure
types are not supported by NonStop SQL/MX.
Types of Keys Used in SQL/MX Tables and Indexes on page 4-4 describes the key
types used with SQL/MX tables and indexes. The Key-Sequenced File Structure on
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.