Logical Key Data Type
The logical key data type allows the DBMS Server or your application to assign a unique key value to each row in a table. Logical keys are useful when an application requires a table to have a unique key, and the columns of the table do not comprise a unique key.
There are two types of logical keys:
• SYSTEM_MAINTAINED--The DBMS Server automatically assigns a unique value to the column when a row is appended to the table. Users or applications cannot change system maintained columns. When the column is created, the DBMS Server assigns it the option NOT NULL WITH DEFAULT. An error is returned if any option other than NOT NULL WITH DEFAULT is specified.
• NOT SYSTEM_MAINTAINED--The DBMS Server does not assign a value to the column when a row is appended. Your application must maintain the contents of the column; users and application programs can change logical key columns that are not system maintained. The default for logical key columns is NOT SYSTEM_MAINTAINED.
Specify the scope of uniqueness for SYSTEM_MAINTAINED logical key columns using the following options:
• TABLE_KEY--Values are unique within the table.
• OBJECT_KEY--Values are unique within the database.
If two or more SYSTEM_MAINTAINED logical key columns of the same type (OBJECT_KEY or TABLE_KEY) are created within the same table, the same value is assigned to all columns of the same type in a given row. Different values are assigned to object and table key columns in the same row, as shown in the following diagram:
TABLE_KEY values are returned to embedded SQL programs as 8-byte strings, and OBJECT_KEY values as 16-byte strings. Values can be assigned to logical keys that are NOT SYSTEM_MAINTAINED using string literals. For example:
INSERT INTO keytable(table_key_column) VALUES('12345678');
Values assigned to TABLE_KEYs must be 8-byte strings; values assigned to OBJECT_KEYs must be 16-byte strings.
In a UTF-8 environment, logical keys must be passed as type BYTE.
Restrictions on Logical Keys
When working with logical keys, be aware of the following restrictions:
• A SYSTEM_MAINTAINED logical key column cannot be created using the CREATE TABLE...AS SELECT statement. A NOT SYSTEM_MAINTAINED data type is assigned to the resulting column.
• The COPY statement cannot be used to load values from a file into a SYSTEM_MAINTAINED column.
