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

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-87
Considerations for CREATE TABLE
This table show the results of specifying or not specifying WITH
CONSTRAINTS on primary key constraints:
When you perform a CREATE TABLE LIKE, whether or not you include the
WITH CONSTRAINTS clause, the target table will have all the NOT NULL
DROPPABLE column constraints that exist for the source table, plus all the
NOT NULL NOT DROPPABLE column constraints that exist for the source
table. They will have different constraint names.
WITH HEADINGS
directs NonStop SQL/MX to use column headings from source-table.
WITH PARTITIONS
directs NonStop SQL/MX to use partition definitions from source-table.
Each new table partition resides on the same volume as its original source-
table counterpart. The new table partitions do not inherit partition names from
the original table. Instead, NonStop SQL/MX generates new names based on
the physical file location.
If you specify the LIKE clause and the PARTITION file-option, you cannot
specify WITH PARTITIONS. If you specify the LIKE clause and the STORE BY
store-option, you cannot specify WITH PARTITIONS. If the
source-table has a partitioned index for a constraint, an index is created for
the constraint on the target table, with attributes that differ from the attributes of
the source table’s index.
Considerations for CREATE TABLE
Reserved Table Names
Table names prefixed by the name of a UMD table are reserved. You cannot create
tables with such names. For example, you cannot create a table named
HISTOGRAMS_MYCOPY.
WITH
CONSTRAINTS
clause?
Source table
has...
Target table will have...
No DROPPABLE
primary key
constraint
No primary key constraint.
Yes DROPPABLE
primary key
constraint
The same DROPPABLE primary key constraint
with a different name.
With or without NOT
DROPPABLE
primary key
constraint
The same NOT DROPPABLE primary key
constraint with a different name.