SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-162
Examples—CREATE TABLE

Only key-sequenced tables can be Format 2 enabled and might have partitions
that are either Format 1 or 2. Relative and entry-sequenced tables can only be
Format 1 enabled and must have Format 1 partitions. If you attempt to specify
FORMAT 2 or PARTITION ARRAY FORMAT2ENABLED for a relative or
entry-sequenced table, you will receive an error. Specifying FORMAT 1 for
relative or entry-sequenced partitions is allowed.

The default partition format is based on the partition array value for
key-sequenced tables. For STANDARD and EXTENDED, the default partition
format is 1. For FORMAT2ENABLED, the default partition format is 2.

If PARTITION ARRAY is explicitly specified, a value of STANDARD or
EXTENDED requires the partition format for the base and all other partitions to
be FORMAT 1.

If the partition array parameter is not explicitly specified and if one or more
partitions are explicitly specified as FORMAT, the result is as if you explicitly
specified PARTITION ARRAY FORMAT2ENABLED. You are only required to
specify either a single partition as FORMAT 2 or PARTITION ARRAY
FORMAT2ENABLED to create a Format 2-enabled table where all partitions
are Format 2.

The partition array, format, and file parameters such as extent sizes must be
consistent for the CREATE TABLE command to succeed. This is true for both
explicitly specified and defaulted values.
Examples—CREATE TABLE

This example creates a table named ORDERS on subvolume $VOL1.SALES. The
catalog also resides on subvolume $VOL1.SALES and the primary key is the
ORDERNUM column.
CREATE TABLE $VOL1.SALES.ORDERS (
ORDERNUM NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
ORDER_DATE NUMERIC (6) NO DEFAULT NOT NULL,
DELIV_DATE NUMERIC (6) NO DEFAULT NOT NULL,
SALESREP NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
CUSTNUM NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
PRIMARY KEY ORDERNUM
)
CATALOG $VOL1.SALES;

This example creates a table with two columns: JOBCODE and JOBDESC. The
table is key-sequenced, and the primary key is JOBCODE. JOBCODE cannot
contain null values because it is used in the primary key.
CREATE TABLE \SYS1.$VOL1.PERSNL.JOB (
JOBCODE DECIMAL (4) UNSIGNED NO DEFAULT,
JOBDESC VARCHAR (18) NO DEFAULT,
PRIMARY KEY JOBCODE )
CATALOG \SYS1.$VOL1.PERSNL ORGANIZATION KEY SEQUENCED;