Neoview SQL Reference Manual (R2.4)

• The IDENTITY column can be the partitioning key or can be part of a compound partitioning
key.
• INSERT...SELECT operations are supported.
• CREATE SET TABLE with an IDENTITY column is supported. Duplicate rows are discarded
without raising error 8102.
• CREATE TABLE AS with GENERATED BY DEFAULT AS IDENTITY column is supported.
Inserting values for a GENERATED ALWAYS AS IDENTITY column is not allowed.
• CREATE TABLE LIKE with an IDENTITY column is supported. The target table will have
the same column and sequence generating attributes as the source table.
• CREATE VOLATILE TABLE with an IDENTITY column is supported.
•
• The internal sequence generator associated with the IDENTITY column will be dropped
when the table with that IDENTITY column is dropped.
Restrictions for IDENTITY Column
These restrictions apply to a column defined as an IDENTITY column. Appropriate error messages
are generated for many of these restrictions.
• Only one IDENTITY column can be used in a table.
• The IDENTITY column must have a NOT NULL constraint. If not specified, the system will
implicitly add the constraint.
• An IDENTITY column definition can support LARGEINT, unsigned INTEGER, and unsigned
SMALLINT data types.
• The NO CYCLE option is the only cycle option supported.
• No support exists for generating IDENTITY values that are unique across multiple tables.
• Only ascending IDENTITY values are supported. IDENTITY values are called ascending if
the increment value is a non-negative value.
• These options can be specified one time only for each table:
— START WITH
— INCREMENT BY
— MAXVALUE
— MINVALUE
— NO CYCLE
• 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 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. In the case of the IDENTITY column being of data type LARGEINT,
the minimum default value will be zero, not -9223372036854775808.
• 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
86 SQL Statements