In the 'Create Index' statement documentation of the 10.1 Reference Guide this
statement is made about creating unique indexes:
Indexes and constraints
Unique, primary key, and foreign key constraints generate indexes that enforce or "back" the constraint (and are thus sometimes called backing indexes). If a column or set of columns has a UNIQUE or PRIMARY KEY constraint on it, you can not create an index on those columns.
However, this SQL succeeds and does create two unique indexes on the same column:
First I ran this SQL:
CREATE TABLE APP.table5 (
col1 CHAR(3) NOT NULL,
col2 INTEGER,
col3 VARCHAR(28) NOT NULL);
col1 CHAR(3) NOT NULL,
col2 INTEGER,
col3 VARCHAR(28) NOT NULL);
Then I created a unique index called idx1 first:
CREATE UNIQUE INDEX "APP"."idx1" ON "APP"."TABLE5" ("COL1" ASC);
Then I added a primary key:
ALTER TABLE "APP"."TABLE5" ADD CONSTRAINT "myprimkey" PRIMARY KEY (COL1);
This added an additional unique index, a backing index:
CREATE UNIQUE INDEX "APP"."SQL050822005949400" ON "APP"."TABLE5" ("COL1" ASC);
So either this is a Derby bug, and the second index should not be
created, or the documentation needs to be updated to say that if a unique index exists,
but a primary key is added to a column with a unique index it will succeed.
If I first create a primary key, which subsequently adds the backing index, then I try to add the unique index it does fail. This behaviour does jive with the documentation.
Thanks,
Susan
