SQL/MX 3.x Reference Manual (H06.22+, J06.11+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—640322-001
2-98
Examples of CREATE TABLE
Consider an RI with an RI action other than NO ACTION or RESTRICT. When the
primary table is deleted and the delete action in the RI of the foreign table is
CASCADE, the row trigger on the foreign table with DELETE as the trigger operation
is fired if there are matching rows in the foreign table. Similarly, when the primary table
is updated and the update action is anything except NO ACTION or RESTRICT, the
row trigger on the foreign table with UPDATE as the trigger operation is fired if there
are matching rows in the foreign table. Statement triggers, if present on the foreign
table, are fired for each update or delete on the primary table.
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)