SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013

C-28

Clustering Keys

A clustering key is the user-defined portion of a primary key that is determined partly

by the user and partly by the system. Values for a clustering key do not need to be

unique, as required for user-defined primary keys. Only key-sequenced tables can

have clustering keys.

To define a clustering key, specify one or more columns in the CLUSTERING KEY

clause of the CREATE TABLE statement. SQL adds a column named SYSKEY (data

type LARGEINT SIGNED and ASCENDING sort order) as the first column of the table

and uses a primary key that consists of the clustering key you specified concatenated

with the SYSKEY. (SYSKEY is the first column of the table but the last column in the

primary key.)

When you add a row to the table, the file system automatically generates a unique

8-byte number as a value for the SYSKEY column, enabling SQL to uniquely identify

the row. You cannot specify the value for the SYSKEY column; it is always supplied by

the file system.

The primary key for a table with a clustering key is the column or columns in the

user-defined clustering key followed by the system-defined SYSKEY column. With

SYSKEY added to the clustering key, the primary key has a unique value for each row.

Because the SYSKEY value cannot be specified by an application, do not use a

clustering key if you need a unique key that can be supplied by your application.

Like other columns in a primary key, columns in a clustering key cannot be updated

and cannot contain null values. The combined length of the columns in a clustering

key, not including the 8-byte SYSKEY column, cannot exceed 247 bytes.

The catalog description of a table with a clustering key reflects the presence of the

8-byte SYSKEY column, but SQL does not display SYSKEY as part of the table unless

a query explicitly selects the SYSKEY column. In a table that includes a SYSKEY

column, for example, this SELECT statement does not display SYSKEY:

SELECT * FROM table-name

In a view definition, however, the same SELECT includes SYSKEY in the view

columns unless you are using a protection view. For more information, see Protection

View on page P-32.

Example—CLUSTERING KEYS

This statement declares a table with a clustering key:

CREATE TABLE CK (SYS_ID SMALLINT, CPU SMALLINT, PIN SMALLINT,

PROG_NAME VARCHAR(34)) CLUSTERING KEY (SYS_ID, CPU, PIN);