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 Tue, 23 Aug 2005 18:20:08 GMT
Hi Satheesh,
 
Thanks for responding to this question.  I will log a doc bug based on my findings.
Essentially I tested 6 cases and came up with the following;
 
Create a table:
 
CREATE TABLE table1 (col1 CHAR(3)not null);
 
Then:
 
1) create index
    create unique index => succeeds
 
2) create unique index
    create index =>  fails
 
3) create index
    create PK => succeeds
 
4) create unique index
    create PK => succeeds
 
5) create PK
    create index => fails
 
6) create PK
   create unique index => fails.
 
Below is a way I think summarizes this, and I'll log the bug requesting this info in
the documentation for the CREATE INDEX statement:
 
 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.
 
be changed to this:
 
If a column or set of columns has a PRIMARY KEY constraint on it, you can not 
create an index on those columns.  If a column or set of columns has a UNIQUE constraint on
it, you can not create an index on those columns, but you can create
a PRIMARY KEY constraint on it.  Addtionally, if this is the case, a backing index
will be created for the PRIMARY KEY constraint so two indexes will now exist on the column
or set of columns that had the UNIQUE constraint on it.
 
Conversely, if a column has an existing index on it you can create a UNIQUE or PRIMARY KEY
constraint on it.
 
Susan

Satheesh Bandaram <satheesh@Sourcery.Org> wrote:
Thanks for the research... I think Derby has this optimization that only catches the case
of duplicate indexes one way. (Where primary/unique constraint is created first) The second
duplicate index that may get created the other way doesn't cause any issues, other than being
redundent and wasting cycles. This is not techincally a bug... a partially implemented optimization.
Mike had mentioned how this optimization can be made more generic last week.

Regarding documentation, it only talks about catching duplicate indexes created after creating
primary/unique constraints. It doesn't mention what happens if an index is created first,
before the constraints. May be docs can be made more clearer, until the optimization is made
more generic.

Satheesh

Susan Cline wrote: 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