SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
2-130
Examples of CREATE TABLE
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,
Col2 INTEGER NOT NULL, PRIMARY KEY(Id_col)
);
*** ERROR[1570] The MAXVALUE for the sequence generator must be
greater than the MINVALUE for IDENTITY column ID_COL.

In this example, none of the sequence generator options are specified; the default
values for all options are used:
start value: 0 (zero)
increment: 1
min value: 0 (zero)
max value: 4294967295
NO CYCLE
CREATE TABLE tbl1 (
Id_col INTEGER UNSIGNED GENERATED BY DEFAULT AS IDENTITY NOT
NULL,
Col2 INTEGER NOT NULL, PRIMARY KEY(Id_col)
);
showddl tbl1;
CREATE TABLE CAT.SCH.TBL1
(ID_COL INT UNSIGNED GENERATED BY DEFAULT AS IDENTITY (START
WITH 0 INCREMENT BY 1 MAXVALUE 4294967295 MINVALUE 0 NO CYCLE)
LOCATION \DMR15.$SYSTEM.ZSDWDPR4.WPBXMX00
-- NOT NULL NOT DROPPABLE
, COL2 INT NO DEFAULT -- NOT NULL NOT DROPPABLE
, CONSTRAINT CAT.SCH.TBL1_697159451_3816 PRIMARY KEY ( ID_COL
ASC) NOT DROPPABLE
, CONSTRAINT CAT.SCH.TBL1_232649451_3816 CHECK
(CAT.SCH.TBL1.ID_COL IS NOT NULL AND CAT.SCH.TBL1.COL2 IS NOT
NULL) NOT DROPPABLE
)
LOCATION \DMR15.$SYSTEM.ZSDWDPR4.S5RXMX00
NAME DMR15_SYSTEM_ZSDWDPR4_S5RXMX00
ATTRIBUTES BLOCKSIZE 4096
STORE BY (ID_COL ASC)
;

This example shows that the IDENTITY column options can be specified in any
order:
CREATE TABLE tbl1 (
Id_col INTEGER UNSIGNED GENERATED BY DEFAULT AS IDENTITY
( START WITH 100 MAXVALUE 1000 INCREMENT BY 2 MINVALUE 50)
NOT NULL,