When adding a UNIQUE constraint UC on two columns C1, C2 to
which already an index IX(C2, C1) exists that index is re-used.
This is an accepted DB2 feature and can be argued to be
semantically correct.
If the order of creation of UC and IX is reversed then the index
UC(C1, C2) will be created to support UC as well as IX. This too
is correct as the index serves a purpose for performance.
As schema that does require "reverse order" (or similar) indexes
to supplement defined constraints is therefore dependent on
correct ordering of object creation.
db2look however does not observe creation order for indexes and
constraints, despite -ct (sort by create time) being specified.
The cause is, table constraint does not have creation time
recorded in catalog view, db2look -CT can not order these
constraint DDLs by creation_time, so just print the DDLs at the
bottom of the script.
Repro Script:
db2 create db test
db2 connect to test
db2 "create table t(c1 int not null, c2 int not null)"
db2 "alter table t add constraint u1 unique (c1, c2)"
db2 "create unique index i1 on t(c2, c1)"
db2 terminate
Repro Output:
$ db2look -d test -ct -e -u user
-- Creating DDL for table(s)
-- This CLP file was created using DB2LOOK Version "10.5"
-- Timestamp: Tue 24 Jun 2014 09:47:08 AM EDT
-- Database Name: TEST
-- Database Manager Version: DB2/LINUXX8664 Version 10.5.4
-- Database Codepage: 1208
-- Database Collating Sequence is: IDENTITY
-- Alternate collating sequence(alt_collate): null
-- varchar2 compatibility(varchar2_compat): OFF
-- Binding package automatically ...
-- Bind is successful
-- Binding package automatically ...
-- Bind is successful
CONNECT TO TEST;
CREATE SCHEMA "USER" ;
CREATE TABLE "USER"."T" (
"C1" INTEGER NOT NULL ,
"C2" INTEGER NOT NULL )
IN "USERSPACE1"
ORGANIZE BY ROW ;
CREATE UNIQUE INDEX "USER"."I1" ON "USER "."T"
("C2" ASC,
"C1" ASC)
COMPRESS NO
INCLUDE NULL KEYS ALLOW REVERSE SCANS ;
ALTER TABLE "USER"."T"
ADD CONSTRAINT "U1" UNIQUE
("C1",
"C2") ;
COMMIT WORK;
CONNECT RESET;
TERMINATE; |
