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-140
Examples of CREATE TABLE
•
This example creates a table stored by primary key. These defaults are in effect:
POS_LOCATIONS is set to $VOL1, $VOL2, $VOL3 and POS_NUM_OF_PARTNS is
set to 3.
CREATE TABLE SALES.ODETAIL
( ordernum NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
partnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
unit_price NUMERIC (8,2) NO DEFAULT NOT NULL,
qty_ordered NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL,
PRIMARY KEY (ordernum, partnum) NOT DROPPABLE )
STORE BY PRIMARY KEY;
NonStop SQL/MX will place the primary partition on $VOL1, the second partition
on $VOL2, and the third partition on $VOL3.
•
This example creates a table stored by primary key. These defaults are in effect:
POS_LOCATIONS is set to $VOL1, $VOL2, $VOL3 and POS_NUM_OF_PARTNS is
set to 5.
CREATE TABLE SALES.ODETAIL
( ordernum NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
partnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
unit_price NUMERIC (8,2) NO DEFAULT NOT NULL,
qty_ordered NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL,
PRIMARY KEY (ordernum, partnum) NOT DROPPABLE )
STORE BY PRIMARY KEY;
NonStop SQL/MX will place the primary partition on $VOL1, the second partition
on $VOL2, the third partition on $VOL3, the fourth on $VOL1, and the fifth on
$VOL2.
•
This example creates a table stored by primary key. This statement includes a
LOCATION clause. These defaults are in effect: POS_LOCATIONS is set to $VOL1,
$VOL2, $VOL3 and POS_NUM_OF_PARTNS is set to 4.
CREATE TABLE SALES.ODETAIL
( ordernum NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
partnum NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
unit_price NUMERIC (8,2) NO DEFAULT NOT NULL,
qty_ordered NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL,
PRIMARY KEY (ordernum, partnum) NOT DROPPABLE )
LOCATION \NODE3.$DATA1
STORE BY PRIMARY KEY;
NonStop SQL/MX will place the primary partition on $DATA1, the second partition
on $VOL1, the third partition on $VOL2, and the fourth partition on $VOL3.
IDENTITY column examples
•
This example shows how to create an IDENTITY column for a simple table. In this
example, the column surrogate_key is defined as the IDENTITY column and is
the primary key of the table t_id_S.
CREATE TABLE t_id_S (surrogate_key LARGEINT GENERATED BY
DEFAULT AS IDENTITY NOT NULL,