db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael J. Segel" <mse...@segel.com>
Subject Re: Ability to create a unique index on the same column, doc bug or "bug" bug?
Date Mon, 22 Aug 2005 22:33:16 GMT
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

Mime
View raw message