NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
S-90
Syskeys
another node has group manager privileges (generalized owner privileges) for objects
and files secured so that their owner can purge them remotely.
Syskeys
A SYSKEY, or system-defined primary key, is a primary key defined by SQL rather
than by the user.
Tables stored in relative and entry-sequenced files or in key-sequenced files without a
user-defined primary key have a primary key defined by SQL and stored in a column
named SYSKEY.
SQL adds the SYSKEY column to the table definition for you; you do not supply it.
SYSKEY is the first column in the table, and its data type depends on the organization
of the file, as shown:
When you insert a record in a table stored in an entry-sequenced 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.
When you insert a record in a table stored in a relative file, you can specify a record
number for the SYSKEY value. If you do not specify a value, the file system supplies a
record number. The SYSKEY column cannot contain a null value.
Because a SYSKEY is defined by the system when a record is inserted, reloading a table
with a SYSKEY (or a clustering key, which uses a SYSKEY) changes the SYSKEY
value for records in the table. Any previous SYSKEY values stored in other tables no
longer point to the proper row after such a load. Moving a partition does not change
SYSKEY values, even though the MOVE operation includes a load operation.
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 4,294,963,199 (for a 4-byte
SYSKEY column).
The catalog description of a table with a SYSKEY reflects the presence of the SYSKEY
column, but SQL does not return the value of the SYSKEY column unless a query
explicitly selects that column. For example, the following SELECT statement would not
display SYSKEY values:
SELECT * FROM table-name
If a view definition explicitly includes the SYSKEY column of a table, however, a
SELECT * on the view does return SYSKEY values.
You cannot partition key-sequenced tables that use SYSKEY as the primary key.
File Organization SYSKEY Data Type SYSKEY Value
Entry-sequenced INTEGER UNSIGNED 4-byte record address
Key-sequenced LARGEINT SIGNED 8-byte unique number
Relative INTEGER UNSIGNED 4-byte record number