SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-165
Examples—CREATE TABLE
, C VARCHAR(300) NO DEFAULT NOT NULL
, PRIMARY KEY A
)
PARTITION (
$VOL2.SUBV1.PARTTIME FIRST KEY TIMESTAMP
"1989-12-1:12:00:00.000000" );

This example column definitions show various combinations of DEFAULT, NULL,
and NOT NULL clauses and their effects.
Column PARTNUM can contain null values. Because no DEFAULT clause is
specified, the column is initialized to a null value when a row is inserted without
supplying a value for PARTNUM:
( ... PARTNUM NUMERIC (4) UNSIGNED ... )
Column DEPTNUM cannot contain null values. The user must supply a nonnull
value when a row is inserted:
(... DEPTNUM NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL ... )
Column ORDERNUM can contain null values. The column is initialized to a system
default value when a row is inserted without supplying a value for ORDERNUM:
( ... ORDERNUM NUMERIC (6) UNSIGNED DEFAULT SYSTEM ... )
Column EMPNUM cannot contain null values. The column is initialized to a system
default value when a row is inserted without supplying a value for EMPNUM:
(...EMPNUM NUMERIC (4) UNSIGNED DEFAULT SYSTEM NOT NULL...)
Column JOBCODE can contain null values. The user must supply a value for
JOBCODE when a row is inserted. The value supplied could be a null value:
( ... JOBCODE NUMERIC (4) UNSIGNED NO DEFAULT ... )

These examples create a Format 2 enabled table with four partitions, all of which
will be Format 2:
CREATE TABLE $VOL1.SALES.ORDERS (
ORDERNUM PIX X(6) NO DEFAULT NOT NULL,
ORDER_DATE PIC X(6) NO DEFAULT NOT NULL,
DELIV_DATE PIC X(6) NO DEFAULT NOT NULL,
SALESREP PIC X(4) NO DEFAULT NOT NULL,
CUSTNUM PIC X(4) NO DEFAULT NOT NULL,
PRIMARY KEY ( ORDERNUM ) )
PARTITION ($VOL2.SALES.ORDERS FIRST KEY “D”,
$VOL3.SALES.ORDERS FIRST KEY “K”,
$VOL4.SALES.ORDERS FIRST KEY “S”)
PARTITION ARRAY FORMAT2ENABLED;
You could specify FORMAT 2 and get the same result:
CREATE TABLE $VOL1.SALES.ORDERS (
ORDERNUM PIX X(6) NO DEFAULT NOT NULL,
ORDER_DATE PIC X(6) NO DEFAULT NOT NULL,
DELIV_DATE PIC X(6) NO DEFAULT NOT NULL,