SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-88
Considerations for CREATE TABLE
Partitions
If there is a possibility that you might need to partition a table in the future, you should
create it with at least one partition. This avoids recompilation if you add more partitions
later.
The LIKE specification
The CREATE TABLE LIKE statement does not create views, owner information, or
privileges for the new table based on the source table. Privileges associated with a
new table created by using the LIKE specification are defined as if the new table is
created explicitly by the current user.
If the source table has any unique or droppable primary key constraints, NonStop
SQL/MX creates indexes for them on the target table. Other indexes on the source
table are not created on the target table.
The LIKE specification ignores triggers.
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.
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.