SQL/MP Installation and Management Guide

Understanding and Planning Database Tables
HP NonStop SQL/MP Installation and Management Guide—523353-004
3-7
Key-Sequenced File Structure
SYSKEY appended to a clustering key. This rule also applies to entry-sequenced
tables but not to relative tables.
•
If you create a table with a SYSKEY column, consistency problems can result if the
values of SYSKEY are used in a column of a user-defined table as a foreign key in
that table. If the original table is reloaded, the SYSKEY values change because the
column is system defined. In this event, any references to these keys in other
tables will no longer point to the correct rows. When referring to keys in other
tables, or for any cases in which a unique key is necessary, always use a user-
defined primary key to avoid dependence upon the value of SYSKEY.
•
SQL/MP primary keys, clustering keys, and alternate keys (indexes) can be
composed of one or more columns of a base table. Each column of a key can be in
either ascending or descending order and can be of a different data type from the
other key columns. Also, the columns need not be contiguous.
•
A user-defined primary key or clustering key facilitates generic locking. Generic
locking enables an application to control the granularity of locks. If you define an
appropriate primary key, your applications can use generic locking to improve
performance.
When defining columns for a primary key, determine whether a specific set of columns
is the most appropriate based on actual use of the table. If a column is used as the
main access path to a table, consider defining it as the primary key or adding it to the
existing primary key as the leftmost column. To determine the main access path,
review transactions that use different access paths to the table.
For example, if an employee table has employee number as its primary key but most
accesses are by employee name, consider defining employee name as the primary
key. The key must include enough information to make it unique (perhaps by including
employee number as the second part of the key). A unique alternate index can help
enforce integrity in this instance.
If you change a column to be the primary key, you can change the former primary key
to an alternate unique index. If the new primary key column is not unique, add a
second column that makes it unique or define it as a clustering key that will have a
system-defined unique column associated with it.
Types of Access
You can access key-sequenced files either sequentially or randomly. Sequential
access is preferable, for example, when generating a report of the currently available
quantity of all parts in an inventory file. Random access is preferable when you want to
identify the vendor of a particular part.
When SQL reads a key-sequenced file by primary key, each read operation retrieves
the record containing the next sequentially higher primary-key value. Similarly, when
SQL reads by a clustering key, each operation retrieves the record containing the next
sequentially higher value in the specified clustering-key field. When SQL reads the file
through an index, each operation randomly accesses the data file.