SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-141
Examples of CREATE TABLE
name CHAR (5) NOT NULL,
primary key(surrogate_key)
)
HASH PARTITION BY(surrogate_key);
•
This example shows the IDENTITY column id_key as part of the clustering key
(STORE BY clause):
CREATE TABLE t_id (id_key LARGEINT GENERATED BY
DEFAULT AS IDENTITY NOT NULL,
name CHAR (256) NOT NULL,
order_number INT UNSIGNED NOT NULL
)
STORE BY (id_key, order_number);
•
This example shows the IDENTITY column id_key as the partitioning key:
CREATE TABLE t_id (id_key LARGEINT GENERATED BY
DEFAULT AS IDENTITY NOT NULL,
name CHAR (256) NOT NULL,
order_number INT UNSIGNED NOT NULL
)
STORE BY (id_key, order_number)
HASH PARTITION BY(id_key);
•
This example shows that the values for the IDENTITY column Id_col will always
be generated by the system. MINVALUE, MAXVALUE, and NO CYCLE will take
default values because they are not specified:
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 a error 3428 indicating that you cannot specify a value for the
IDENTITY column defined as GENERATED ALWAYS.
*** ERROR[3428] IDENTITY column ID_COL defined as GENERATED ALWAYS
cannot accept values specified by the user.
•
This example 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,
Note. In SQL/MX, 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.