Neoview SQL Reference Manual (R2.4 SP2)

Available Values for an IDENTITY Column
The IDENTITY column can be defined as signed LARGEINT, unsigned INTEGER, and unsigned
SMALLINT. Each data type has a natural maximum value. The settings chosen for START WITH,
MINVALUE, MAXVALUE, and INCREMENT BY create a valid range of available numbers
before a maximum is reached. The settings MAXVALUE and INCREMENT BY can be altered
using ALTER TABLE ALTER COLUMN to change an artificially low range created during the
CREATE TABLE process. For more information, see the “ALTER TABLE Statement” (page 44).
Duplicate Values For an IDENTITY Column
Duplicates can be generated for IDENTITY columns if the IDENTITY column is defined as
GENERATED BY DEFAULT AS IDENTITY and you specify a duplicate value for the column.
Considerations
• If you specify GENERATED BY DEFAULT AS IDENTITY, values for the IDENTITY column
are generated by default. If you specify a value for the IDENTITY column, Neoview SQL
uses that value and does not generate a unique value for that row.
• IDENTITY columns can be the PRIMARY KEY or can be part of a compound clustering key.
You can alter the MAXVALUE and INCREMENT BY sequence generator options of an
IDENTITY column using the ALTER TABLE ALTER COLUMN statement.
• 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
88 SQL Statements