SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
2-131
Examples of CREATE TABLE
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.
•
In this example, an IDENTITY column is defined on a range partitioned table:
CREATE TABLE tab1(a LARGEINT GENERATED ALWAYS AS IDENTITY
(START WITH 51 INCREMENT BY 2 MAXVALUE 55 MINVALUE 50 NO CYCLE)
NOT NULL,
b INT UNSIGNED NOT NULL,
c INT NOT NULL,
d INT NOT NULL,
e INT NOT NULL,
PRIMARY KEY(a, B, C) )
LOCATION $data10