Neoview SQL Reference Manual (R2.5)

• 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
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);
CREATE TABLE Statement 89