Neoview SQL Reference Manual (R2.2)

RULES
• Only one IDENTITY column can be used in a table.
• Values for the IDENTITY column are generated by default. If you specify a value for the
IDENTITY column, the system uses that value and does not generate a unique value for
that row.
• The IDENTITY column must have a NOT NULL constraint. If not specified, the system will
implicitly add the constraint.
• The IDENTITY column property is retained on the target table for CREATE TABLE LIKE...
statements.
CONSIDERATIONS
• IDENTITY columns can be the PRIMARY KEY or can be part of a compound clustering key.
You must have a unique index on the IDENTITY column. It is recommended that you assign
the IDENTITY column as the clustering key to avoid an extra index on the table. This avoids
index maintenance.
• The IDENTITY column can be the partitioning key or can be part of a compound partitioning
key.
• You can specify a SIGNED LARGEINT value for an IDENTITY column. The system generates
only positive values greater than 1023.
RESTRICTIONS
These restrictions apply to a column defined as an IDENTITY column. Appropriate error messages
are generated for each of these restrictions.
• You cannot add an IDENTITY column using the ALTER TABLE statement.
• INSERT...SELECT operations are supported.
• You cannot define a trigger to insert into an IDENTITY column.
• An IDENTITY column can only be defined on a LARGEINT column. You can specify a
SIGNED LARGEINT value if you want to use the negative values.
• Expressions are not allowed as IDENTITY column values. You must specify the keyword
DEFAULT or supply a LARGEINT value.
• For an IDENTITY column, the tuple list cannot have mixed user and DEFAULT values
specified. You must specify values for all in the tuple list or specify DEFAULT for all in the
tuple list. For example, error 3414 is raised in the following case:
INSERT INTO t id_s values (DEFAULT,’1’,1),
(DEFAULT,’2’,2),
(50,’3’,3),
(DEFAULT,’4’,4)
(DEFAULT,’5’,5);
EXAMPLES
• This example shows how to create an IDENTITY column for a simple table. In this example,
the column surrogate_key is defined as the IDENTITY column and is the primary key
(clustering key).
CREATE TABLE t_id_S (surrogate_key LARGEINT GENERATED BY
DEFAULT AS IDENTITY NOT NULL,
name CHAR (5) NOT NULL,
primary key(surrogate_key)
)
HASH PARTITION BY(surrogate_key);
• This example shows IDENTITY column surrogate_key as part of the clustering key. The
surrogate key column must have a unique index on it.
CREATE TABLE t_id (surrogate_key LARGEINT GENERATED BY
DEFAULT AS IDENTITY NOT NULL,
76 SQL Statements