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-131
Examples of CREATE TABLE
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
PARTITION BY(a,B,C)(
ADD FIRST KEY (60, 0, 1)
LOCATION $data12
, ADD FIRST KEY (70, 0, 1)
LOCATION $data13
, ADD FIRST KEY (65, 0, 1)
LOCATION $data14
);

This example shows how to specify the location for the SG Table using the
LOCATION clause:
CREATE TABLE tab1(a LARGEINT GENERATED ALWAYS AS IDENTITY
(START WITH 51 INCREMENT BY 2 MAXVALUE 55 MINVALUE 50 NO CYCLE)
LOCATION $data14,
b INT UNSIGNED NOT NULL,
c INT NOT NULL);
showddl tab1;
CREATE TABLE CAT.SCH.TAB1
(A LARGEINT GENERATED ALWAYS AS IDENTITY
(START WITH 51 INCREMENT BY 2 MAXVALUE 55 MINVALUE 50 NO
CYCLE)
LOCATION \DMR15.$DATA14.ZSDWDPR4.GQ4QB600
-- NOT NULL NOT DROPPABLE
,B INT UNSIGNED NO DEFAULT
-- NOT NULL NOT DROPPABLE
,C INT NO DEFAULT -- NOT NULL NOT DROPPABLE
, CONSTRAINT CAT.SCH.TAB1_156576774_5816 CHECK
(CAT.SCH.TAB1.A IS NOT NULL
AND CAT.SCH.TAB1.B IS NOT NULL AND CAT.SCH.TAB1.C IS NOT
NULL) NOT DROPPABLE
)
LOCATION \DMR15.$SYSTEM.ZSDWDPR4.DDBRB600
NAME DMR15_SYSTEM_ZSDWDPR4_DDBRB600