db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Susan Cline <home4...@pacbell.net>
Subject Re: Ability to create a unique index on the same column, doc bug or "bug" bug?
Date Mon, 22 Aug 2005 23:53:35 GMT
Hi Michael,
 
To answer your questions:

Did Derby actually create the second index?
 
    Yes.

Also when you altered the table, what happened to the initial index? 
 
        Not sure what you mean by this ... nothing happened, it is still there.

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?
 
   Yes, I did drop it and it was successful.

If you do, what happens to your second INDEX?
 
   The second index is still there, and I can successfully insert and select from the table.
 
 
As I said above, here is what happens if I reverse the order:
 
1) Create the table
 
2) Alter the table to add the primary key constraint:
     I have a constraint called myprimkey2
     I have a unique index called SQL050822044536100
 
3) Then I try to create another unique index called idx2 with this SQL:
 

CREATE UNIQUE INDEX "APP"."idx2" ON "APP"."TABLE2" ("COL1" ASC);

and it fails.


SQLState=01504 The new index is a duplicate of an existing index: SQL050822044536100. : 
I think it is a bug, what do you think?
 
Thanks,
 
Susan


"Michael J. Segel" <msegel@segel.com> wrote:
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