SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

The rules for creating and altering tables are:
• To create an SQL/MX table or other object (including index, view, SPJ, or SQL/MP alias),
you must own its schema or be a super ID user acting on behalf of the owner.
• To alter a table, you must own its schema or be a super ID user acting on behalf of the owner.
For more information, see the “Access Privileges for SQL/MX Database Objects” (page 79).
Creating and Using Keys
Create and use these keys for SQL/MX tables and their indexes:
• Primary key. See “Creating and Using a Primary Key” (page 81).
• Clustering key. See “Creating and Using a Clustering Key” (page 81).
• SYSKEY. See “Using the SYSKEY” (page 82).
• Partially decoupling the clustering and partitioning keys. See “Partially Decoupling the Clustering
Key and the Partitioning Key” (page 83).
• Partitioning key. See “Creating and Using a Partitioning Key” (page 83) and “Creating Indexes
for SQL/MX Tables” (page 96).
Creating and Using a Primary Key
A primary key is the column or set of columns that defines a unique key for a table. Primary key
columns cannot contain nulls, so each one must be declared NOT NULL.
Use the PRIMARY KEY constraint clause in CREATE TABLE to specify the table columns of a primary
key. For NonStop SQL/MX, you use the primary key to:
• Specify one or more columns that uniquely define and identify each row.
• Provide an implicit method for defining the columns of the clustering key, but only if the primary
key is NOT DROPPABLE. You can use a primary key that is NOT DROPPABLE as the clustering
key. If the primary key is droppable, you cannot use it as the clustering key.
The PRIMARY KEY constraint is one of several types of constraints that you define for an SQL/MX
table. Each table or index can have only one PRIMARY KEY constraint but multiple occurrences of
other types of constraints. For more information, see the SQL/MX Reference Manual.
Example for Creating an SQL/MX Table With a User-Defined Primary Key
This example creates a single-partition SQL/MX table with a user-defined primary key:
CREATE TABLE CAT1.SCHEMA1.ORDERS
(ORDERNUM DECIMAL (6) UNSIGNED NO DEFAULT NOT NULL,
ORDER_DATE DATE NO DEFAULT NOT NULL,
DELIV_DATE DATE NO DEFAULT NOT NULL,
SALESREP DECIMAL (4) UNSIGNED,
CUSTNUM DECIMAL (4) UNSIGNED NO DEFAULT NOT NULL,
PRIMARY KEY (ORDERNUM))
ATTRIBUTES EXTENT (100,100)
MAXEXTENTS 24;
Creating and Using a Clustering Key
The clustering key consists of one or more columns (fields) whose values uniquely identify the rows
of a table and determine the order in which the rows are stored and retrieved by the DP2 disk
process. The combined value of all clustering key columns for a given row acts as the logical row
identifier (row-ID) for that row, and so must be unique for the table.
When you create an SQL/MX table, you specify the clustering key column or columns that the DP2
process uses to access table records.
Creating SQL/MX Tables 81