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-137
Examples of CREATE TABLE
Usage and Performance
The RI actions CASCADE, SET NULL, and SET DEFAULT enable you to maintain
data integrity between tables. Performing RI actions is resource-intensive because
indexes and multiple tables are involved, which can result in a significant drop in
performance of queries when a large dataset is involved. Therefore, it is important that
you consider the performance implication while defining RI relationships.
Examples of CREATE TABLE
•
This example creates a table stored by primary key. The clustering key is the
primary key.
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;
•
This example creates a table stored by the key column list. The clustering key is
ordernum, partnum, SYSKEY.
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)
STORE BY (ordernum, partnum);
•
This example creates a table stored by the SYSKEY. The clustering key is the
SYSKEY, type LARGEINT.
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)
;
•
This example creates a table like the JOB table with the same constraints:
CREATE TABLE SAMDBCAT.PERSNL.JOB_CORPORATE
LIKE SAMDBCAT.PERSNL.JOB WITH CONSTRAINTS;
•
This example creates table tab1 with partitions named partition1 and
partition2. It then creates table tab2 like tab1 with partitions. tab2’s
partitions have different names than the partitions on tab1.
•
Create tab1:
>>Create table tab1
( a INT not null PRIMARY KEY, b INT)
range partition by (a)