SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-88
Considerations for CREATE TABLE
Storage Order and the LIKE Specification
The STORE BY clause determines the storage order of the records in the new table:
Audited and Nonaudited Tables
NonStop SQL/MX does not support nonaudited SQL/MX tables, but scenarios exist
that require nonaudited tables. For example, suppose that you want updates to occur
even if the operation is rolled back for logging purposes. In this case, you should use
NonStop SQL/MP to create a nonaudited SQL/MP table.
Authorization and Availability Requirements
To create a table, you must own its schema or be the super ID.
To create a constraint on the table that refers to a column in another table, you must
have REFERENCES privileges on that column and access to the table that contains
the column. If the constraint refers to the other table in a query expression, you must
also have SELECT privileges on the other table.
Reduced Space Requirements for NOT DROPPABLE
Constraints
Using the NOT DROPPABLE option on a NOT NULL constraint reduces the space
required for the table. A column that allows nulls—or that might allow nulls at some
later time—uses two extra bytes in each row to store the null indicator. If you specify
that the NOT NULL constraint is NOT DROPPABLE, NonStop SQL/MX creates the
table without these extra bytes.
Using the NOT DROPPABLE option on a PRIMARY KEY or using STORE BY
PRIMARY KEY reduces the space required for the table and eliminates the need to
create an index for accessing the table by primary key.
Constraints Implemented With Indexes
NonStop SQL/MX uses indexes to implement all UNIQUE constraints, the foreign key
portion of all referential constraints, and any PRIMARY KEY constraints that are not
enforced by the clustering key. Necessary indexes are automatically created when you
create a table with these constraints. If you add a constraint to an existing table,
NonStop SQL/MX checks if an existing index can be used to implement the constraint,
creating a new index (if possible, with the same name as the constraint) if needed.
STORE BY PRIMARY KEY The new table is ordered by the primary key of
the source table.
STORE BY key-column-list The new table is ordered by the new
key-column-list.
No STORE BY clause The new table is ordered by the storage key of
the source table.