db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Susan Cline <home4...@pacbell.net>
Subject Ability to create a unique index on the same column, doc bug or "bug" bug?
Date Mon, 22 Aug 2005 20:33:21 GMT
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);
 
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

Mime
View raw message