Neoview SQL Reference Manual (R2.3)

These restrictions apply to a column defined as an IDENTITY column. Appropriate error messages
are generated for each of these restrictions.
• You cannot add an IDENTITY column using the ALTER TABLE statement.
• INSERT...SELECT operations are supported.
• You cannot define a trigger to insert into an IDENTITY column.
• An IDENTITY column can only be defined on a LARGEINT column. You can specify a
SIGNED LARGEINT value if you want to use the negative values.
• Expressions are not allowed as IDENTITY column values. You must specify the keyword
DEFAULT or supply a LARGEINT value.
• For an IDENTITY column, the tuple list cannot have mixed user and DEFAULT values
specified. You must specify values for all in the tuple list or specify DEFAULT for all 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);
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. The
surrogate key column must have a unique index on it.
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);
create unique index sk_idx on t_id(surrogate_key);
• This example shows the IDENTITY column surrogate_key as the partitioning key. Note
that for this release, the surrogate key column must have a unique index on it:
NOTE: In Neoview SQL, the partitioning key must be a subset of the clustering key. In the
case of a table with a single column clustering key, the partitioning key must be the same
as 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);
create unique index sk_idx on t_id(surrogate_key);
CREATE TABLE Statement 77