SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

SQL/MX Language Elements
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
6-63
Primary Keys
For a nonunique index, the clustering key of the index is composed of columns
specified in create index and the clustering key of the table. The clustering key of the
index cannot exceed 2010 bytes for 4K blocks and 2048 bytes for 32K blocks.
Because the entire row of the index is the clustering key of the index, the entire row of
the index cannot exceed 2010 bytes for 4K blocks and 2048 bytes for 32K blocks. For
more information, see Table 6-2
on page 6-61.
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-71 and ALTER INDEX Statement on
page 2-8.
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-60 and Table 6-2 on page 6-61 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.