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

MXCI Commands
HP NonStop SQL/MX Reference Manual—523725-004
4-68
Considerations for SHOWDDL
•
When used on an SQL/MP table through an SQL/MP alias, SHOWDDL displays
the DDL of the SQL/MP table using equivalent SQL/MX syntax.
Differences Between SHOWDDL Output and Original DDL
•
SHOWDDL displays SQL/MX system-created indexes as user-created indexes. In
the output of SHOWDDL, each system-created index is preceded by the comment
'--The following index is a system-created index--'. Because
you cannot explicitly create a system-created index, feeding the output of a
system-created index back into MXCI results in a user-created index.
•
All column constraints (NOT NULL, UNIQUE, PRIMARY KEY, CHECK,
REFERENCES) are transformed into table constraints. For NOT NULL constraints,
“NOT NULL [NOT DROPPABLE]” is included in the column definitions but is
commented out. All NOT NULL NOT DROPPABLE constraints are consolidated
into a single check constraint, while NOT NULL DROPPABLE column constraints
remain in separate check constraints.
•
Each droppable constraint that creates an index (droppable primary key and
unique constraints) is moved out of the CREATE TABLE statement and
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 due to
a partition management operation, and the DDL for the offline partitions is
commented out. The entire partition clause is commented out if all 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.