Neoview SQL Reference Manual (R2.4 SP2)

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.
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);
• This example shows that the values for the IDENTITY column Id_col will always be generated
by the system. The following sequence generator options will take default values because
they are not specified: MINVALUE, MAXVALUE, and NO CYCLE.
CREATE TABLE tbl1 (
Id_col INTEGER UNSIGNED GENERATED ALWAYS AS IDENTITY
(
START WITH 1
INCREMENT BY 2) NOT NULL,
Col2 INTEGER NOT NULL, PRIMARY KEY(Id_col)
);
INSERT INTO tbl1 values (DEFAULT, 10), (DEFAULT, 20), (DEFAULT, 30);
will result in the following rows inserted into table tbl1; (1,10), (3,20), (5,30).
INSERT INTO tbl1 values (15, 10);
will result in an error indicating that you cannot specify a value for the IDENTITY column
defined as GENERATED ALWAYS.
• This statement fails with an error indicating that the start value must be less than the
MAXVALUE and greater than the MINVALUE.
CREATE TABLE tbl1 (
Id_col INTEGER UNSIGNED GENERATED BY DEFAULT AS IDENTITY
(
START WITH 100
INCREMENT BY 2
MAXVALUE 10
MINVALUE 50) NOT NULL,
Col2 INTEGER NOT NULL, PRIMARY KEY(Id_col)
);
90 SQL Statements