Neoview SQL Reference Manual (R2.4 SP2)

• The START WITH value must be less than or equal to the MAXVALUE and greater than or
equal to the MINVALUE for ascending sequence generator. If the START WITH option is
not specified, the minimum value is the minimum value of the data type of the IDENTITY
column or, in the case of the IDENTITY column being of data type LARGEINT, the minimum
default value will be zero, not -9223372036854775808.
• The INCREMENT BY value cannot be 0 (zero) or greater than the MAXVALUE. If the
INCREMENT BY value is not set, the default value is 1.
• If the MAXVALUE option is not specified, the maximum value is the maximum value of
the data type of the IDENTITY column. The value of the MAXVALUE option must be greater
than the value of the MINVALUE. The MAXVALUE option must be a valid numeric value.
• If NO MINVALUE is specified, the minimum value is the minimum value of the data type
of the IDENTITY column. In the case of the IDENTITY column being of data type LARGEINT,
the minimum default value will be zero, not -9223372036854775808. If the MINVALUE
option is not specified, then the minimum value is the minimum value of the data type of
the IDENTITY column.
• MERGE operations on tables which have an IDENTITY column are not allowed.
• Mixed DML and DDL operations performed under the same user transaction are not
supported for an INSERT operation that contains an IDENTITY column. Errors can be
experienced. TMF Error 73 can be experienced because the DDL and DML operations share
the same user transaction on a table lock on the internal sequence generator table.
• For a table with only one column, which is an IDENTITY column, the tuple list cannot have
only DEFAULT values. Error 3431 will be raised. You must specify the input values. For
example, this statement raises error 3431:
INSERT INTO t1 VALUES (DEFAULT), (DEFAULT);
• You cannot add an IDENTITY column using the ALTER TABLE statement.
• Expressions involving the keyword DEFAULT are not allowed as IDENTITY column values.
You must specify the keyword DEFAULT or supply a valid value. Error 3411 will be raised
if an expression is specified for an IDENTITY column value.
• UPDATE operations on IDENTITY columns defined as GENERATED ALWAYS AS
IDENTITY are not supported.
• For IDENTITY columns defined as type LARGEINT, the maximum value is
9223372036854775806, one off of the true maximum.
• The restriction reserving numeric values 0-1023 for an IDENTITY column is removed.
• The restriction requiring a unique index on the IDENTITY column is removed.
• Time-based random number generated values are not supported.
• NEXT VALUE FOR table-name function to obtain the next value for the IDENTITY column
is not supported in this release.
• For an IDENTITY column, the tuple list cannot have mixed user and DEFAULT values
specified. You must specify values for all tuples in the tuple list or specify DEFAULT for all
tuples 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);
IDENTITY Column 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 Statement 89