SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

MXCI Commands
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
4-85
Considerations for SHOWDDL

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.

If there are two not null droppable constraints on the same user added column,
only one of these is displayed.