SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-59
Primary Keys
For a unique index, the clustering key of the index is composed of the first of these
items. The clustering key of the index cannot exceed 255 bytes, but the entire row
(including the clustering key of the index) can contain up to 510 bytes.
For a nonunique index, the clustering key of the index is composed of both items. The
clustering key cannot exceed 255 bytes. Because the clustering key includes all the
columns in the table, each row is also limited to 255 bytes.
For varying-length character columns, the length referred to in these byte limits is the
defined column length, not the stored length. (The stored length is the expanded
length, which includes two extra bytes for storing the data length of the item.)
See CREATE INDEX Statement on page 2-52 and ALTER INDEX Statement on
page 2-7.
Primary Keys
A primary key is the column or set of columns that define the uniqueness constraint for
a table. The columns cannot contain nulls, and there is only one primary key constraint
on a table.
SYSKEYs
A SYSKEY (or system-defined clustering key) is a clustering or storage key defined by
NonStop SQL/MX rather than by the user. Tables stored in files or in key-sequenced
files without a user-defined clustering key have a clustering key defined by
NonStop SQL/MX and stored in a column named SYSKEY. Its type is LARGEINT
SIGNED.
To establish the clustering key, in some cases NonStop SQL/MX appends a SYSKEY
to ensure uniqueness. See Table 6-1 on page 6-56 and Table 6-2 on page 6-57 for the
cases in which a SYSKEY is appended.
When you insert a record in a table stored in a file or in a key-sequenced file with a
SYSKEY column, the file system automatically generates a value for the SYSKEY
column. You cannot supply the value.
Selecting SYSKEY
You cannot update values in the SYSKEY column of any table, but you can use the
SELECT statement to query SYSKEY values. If SYSKEY is provided in the value list or
for a query, the value range allowed is 0 through 2**63 -1 (approximately 9.223 times
10 to the eighteenth power).
A query must explicitly select the SYSKEY column. For example, this SELECT
statement does not display SYSKEY values:
SELECT * FROM table-name
However, if a view definition explicitly includes the SYSKEY column of a table, a
SELECT * on the view does return SYSKEY values.