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-142
Examples of CREATE TABLE
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,
Col2 INTEGER NOT NULL, PRIMARY KEY(Id_col)
);
•
This example fails with an error stating that a table can have only one IDENTITY
column.
CREATE TABLE T (id_key LARGEINT GENERATED BY DEFAULT AS IDENTITY
NOT NULL PRIMARY KEY,
name CHAR (256) NOT NULL,
order_number LARGEINT GENERATED BY DEFAULT AS IDENTITY NOT
NULL)
HASH PARTITION BY(id_key);
*** ERROR[1511] There can only be one IDENTITY column for a table.