SQL/MP Installation and Management Guide

Understanding and Planning Database Tables
HP NonStop SQL/MP Installation and Management Guide—523353-004
3-2
Primary Keys
The following text describes primary keys and then discusses the structures of the
three types of files. An understanding of these structures can help you plan for the best
use of disk storage space when sizing your database, implementing economical table
access methods, and analyzing various performance trade-offs. This understanding is
also essential for anyone using the FILCHECK or TANDUMP utility to operate on
physical file structures.
Primary Keys
All SQL/MP table organizations have a unique primary key. Key values affect the order
in which rows are stored and retrieved. The primary key also serves as the physical
primary key for the DP2 disk process.
At the physical level, a key is a field or group of fields the system can use to order
records and to identify records for processing. The primary key can be user-defined,
system-defined, or a combination of both.
User-Defined Primary Key
A user-defined primary key consists of one or more columns (fields) whose values
uniquely identify the rows of a table and determine the order in which the rows are
stored. The combined value of all primary-key columns must be unique for each row in
the table. You can define the primary key by using the PRIMARY KEY clause in the
CREATE TABLE statement.
Each column has an ordering attribute, either ASCENDING or DESCENDING, that
determines the order for storing and retrieving the rows.
For multicolumn (composite) keys, if multiple rows share the same value for the first
key column, the value in the second key column is used to determine the order for
storing or retrieving the rows. If multiple rows share the same values for the first and
second key columns, the third key column is used to determine the order, and so forth.
The length of a primary key cannot exceed 255 bytes. To calculate the length, find the
sum of the lengths in bytes of the columns that make up the key. For the length of a
varying-length column (VARCHAR, NCHAR VARYING), use the number of bytes
defined for the column only; do not include the two-byte descriptor. In a primary key for
Note. In this discussion of keys, do not confuse the physical primary key with the logical SQL
primary key. The differences are:
•
The physical primary key is the key used by the DP2 disk process to access records within
the file. Within a record, this key must be unique. A key-sequenced file has only one
physical primary key.
•
The primary key is the key recognized and used at the logical level by ANSI standard SQL.
This key is specified in the PRIMARY KEY clause of the CREATE TABLE statement or
partially specified in the CLUSTERING KEY clause. The value of the primary key must be
unique for each row. If you specify a PRIMARY KEY clause, the physical primary key is
also based upon this specification. But if you do not specify a PRIMARY KEY clause, the
RDBMS generates part or all the physical primary key.