On Monday 22 August 2005 15:33, Susan Cline wrote:
> 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
Ok,
Did Derby actually create the second index?
Also when you altered the table, what happened to the initial index?
Depending on what you find, there may be a bug. If there is a bug, it would
have to deal with the ALTER TABLE statement where you added a constraint.
Can you also try to drop your initial index?
If you do, what happens to your second INDEX?
--
Michael Segel
Principal
MSCC
(312) 952-8175
