SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-11
Determining the Organization of the Physical File
For information about loading base tables, see Guidelines for Loading Tables on
page 8-8.
Determining the Organization of the Physical File
When you create a table, you can use the ORGANIZATION clause in the CREATE
TABLE statement to organize the physical file. The ORGANIZATION clause is optional.
The file organization can be key-sequenced, entry-sequenced, or relative. If you do not
choose a file organization by using the ORGANIZATION clause, the organization
defaults to key-sequenced.
Creating Key-Sequenced Tables
When you define a key-sequenced table, you also define the primary key used to
access rows in the table. The data type, physical ordering, and primary key type play a
role in the primary access sequence of the table.
Defining Primary Keys
Use these commands to define primary keys:
•
User-defined primary key: specify columns of the primary key in the PRIMARY
KEY clause of the CREATE TABLE statement. A user-defined primary key can
include a number of contiguous or noncontiguous columns but cannot exceed 255
bytes.
•
System-defined primary key: this is the default type of key generated by SQL/MP if
you do not specify a PRIMARY KEY or CLUSTERING KEY clause.
•
Clustering key: specify columns of the primary key in the CLUSTERING KEY
clause of the CREATE TABLE statement. To this group of columns, SQL/MP
appends a SYSKEY column to form a unique primary key.
Creating a Key-Sequenced Table With a User-Defined Primary Key
This example creates a key-sequenced table with a user-defined primary key on the
current default subvolume and registers the table in the current default catalog. You
can also use the ENV command to list the current environment before using the
CREATE TABLE command.
>> CREATE TABLE ORDERS
+> (ORDERNUM DECIMAL (6) UNSIGNED NO DEFAULT NOT NULL,
+> ORDER_DATE DATETIME YEAR TO DAY NO DEFAULT NOT NULL,
+> DELIV_DATE DATETIME YEAR TO DAY NO DEFAULT NOT NULL,
+> SALESREP DECIMAL (4) UNSIGNED DEFAULT SYSTEM,
+> CUSTNUM DECIMAL (4) UNSIGNED NO DEFAULT NOT NULL,
+> PRIMARY KEY ORDERNUM)
+> EXTENT (100,100)