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

MXCI Commands
HP NonStop SQL/MX Reference Manual—540440-003
4-72
Examples of SHOWDDL
Examples of SHOWDDL
•
This is an example of SHOWDDL on an SQL/MX table that contains unique and
primary key constraints:
>>CREATE TABLE CAT.SCH.T1
(N INT NOT NULL,
C INT NOT NULL UNIQUE,
CONSTRAINT PK PRIMARY KEY (N) NOT DROPPABLE)
STORE BY (C DESC, N)
ATTRIBUTE MAXEXTENTS 600;
>>SHOWDDL CAT.SCH.T1;
CREATE TABLE CAT.SCH.T1
(
N INT NO DEFAULT -- NOT NULL NOT DROPPABLE
, C INT NO DEFAULT -- NOT NULL NOT DROPPABLE
, CONSTRAINT CAT.SCH.PK PRIMARY KEY (N ASC) NOT DROPPABLE
, CONSTRAINT CAT.SCH.T1_102261179_0003 CHECK
(CAT.SCH.T1.N IS NOT NULL AND
CAT.SCH.T1.C IS NOT NULL) NOT DROPPABLE
)
LOCATION \FIGARO.$DATA1.ZSDQXXBK.B7VVVW00
NAME FIGARO_DATA1_ZSDQXXBK_B7VVVW00
ATTRIBUTES MAXEXTENTS 600
STORE BY (C DESC, N ASC)
;
-- The following index is a system created index --
CREATE UNIQUE INDEX T1_102261179_0004 ON CAT.SCH.T1
(
C ASC
)
LOCATION \FIGARO.$DATA2.ZSDUXXBK.B7VVVW00
NAME FIGARO_DATA2_ZSDQXXBK_B7VVVW00
ATTRIBUTES MAXEXTENTS 600
;
-- The following index is a system created index --
--CREATE UNIQUE INDEX PK ON CAT.SCH.T1
-- (
-- N ASC
-- )
-- LOCATION \FIGARO.$DATA1.ZSDXXXBK.B7VVVW00
-- NAME FIGARO_DATA1_ZSDQXXBK_B7VVVW00
-- ATTRIBUTES MAXEXTENTS 600
-- ;
ALTER TABLE CAT.SCH.T1
ADD CONSTRAINT CAT.SCH.T1_102261179_0004 UNIQUE
(C) DROPPABLE;
Note how the unique constraint is moved out of the CREATE TABLE statement and
into an ALTER TABLE statement, how the index supporting the unique constraint
precedes the creation of the unique constraint, and how the index supporting the
not droppable primary key is commented out because a system created index
would be implicitly created.