Neoview SQL Reference Manual (R2.4)

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 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.
CREATE TABLE t_id (surrogate_key LARGEINT GENERATED BY
DEFAULT AS IDENTITY NOT NULL,
name CHAR (256) NOT NULL,
order_number INT UNSIGNED NOT NULL,
primary key (surrogate_key,order_number)
)
HASH PARTITION BY(surrogate_key, order_number);
• This example shows the IDENTITY column surrogate_key as the partitioning key.
CREATE TABLE Statement 87