SQL/MX 2.x Reference Manual (H06.04+)

MXCI Commands
HP NonStop SQL/MX Reference Manual—540440-003
4-68
Considerations for SHOWDDL
Considerations for SHOWDDL
•
SHOWDDL cannot replicate the original object exactly.
For ways in which the output of SHOWDDL can differ from the original DDL used
to create an object, see Differences Between SHOWDDL Output and Original DDL
on page 4-68.You can use SHOWDDL only within an MXCI session.
•
SHOWDDL requires that TMF, NonStop SQL/MX, and MXCI be available and
running on the system.
•
SHOWDDL displays all output in the English (ISO88591) character set only.
•
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.