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-125
Examples of CREATE TABLE
Utilities
The utilities Backup/Restore, MXExportDDL/MXImportDDL, and NSM web support the
newly added RI actions CASCADE/SET NULL/SET DEFAULT in addition to NO
ACTION and RESTRICT.
The utilities DUP and PurgeData retain their existing behavior. The DUP utility does not
support the RI constraints duplication and Purgedata does not allow you to purge data
from a referred 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)
;