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

MXCI Commands
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-004
4-86
Considerations for SHOWDDL
encapsulated in a separate ALTER TABLE ADD CONSTRAINT statement.
Creating an index before creating the constraint that is dependent on the index
allows the details of the index to be specified explicitly.
•
Check constraints are moved out of the CREATE TABLE statement and
encapsulated in a separate ALTER TABLE ADD CONSTRAINT statement.
•
In cases where an index is created by the system to support a not droppable
primary key constraint, the DDL of this system-created index is commented out
(each line is preceded by “--”). Unlike droppable constraints, a not droppable
primary key constraint affects the structure of a table and therefore cannot be
moved from the CREATE TABLE statement and into an ALTER TABLE ADD
CONSTRAINT statement.
Consequently, if a system-created index is implicitly created by the system to
support a not droppable primary key constraint, the DDL output for explicitly
creating such an index must be commented out, or a duplicate index results.
•
SHOWDDL generates ALTER TABLE ADD COLUMN statements for each column
that was added to the table. SHOWDDL also generates the comment '--The
partition is offline-' before the DDL of each partition that is offline
because of a partition management operation, and the DDL for the offline partitions
is commented out. The entire partition clause is commented out if all of the
partitions are offline.
•
The PIC data type is stored as CHAR, DECIMAL, or NUMERIC in
NonStop SQL/MX. SHOWDDL, therefore, displays these data types in place of the
PIC data type.
•
The NCHAR data type is displayed as a CHAR CHARACTER SET
default-char-set showing the current default national character set (either
UCS2 or ISO88591.)
•
All ANSI names in the output are fully qualified.
•
All physical location names are fully expanded.
•
SHOWDDL displays constraint names even though they might not have been
specified during the creation of the constraint.
•
STORE BY is displayed even though it might not have been explicitly stated in the
creation of the table.
•
The ordering of the primary key (ASC/DESC) might differ from that of the original
DDL because it might be changed by the STORE BY ordering.
•
If NO HEADING is specified for a column, NonStop SQL/MX stores it as HEADING
‘‘ (blank,) which SHOWDDL displays.
•
If the column name, which is stored as an upshifted string unless it is delimited, is
identical to the heading (case-sensitive), NonStop SQL/MX treats it as if no
heading was entered. SHOWDDL does not display a heading.