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 Tue, 23 Aug 2005 19:00:26 GMT
On Tuesday 23 August 2005 13:20, Susan Cline wrote:

All,

Sorry to top post, but this is not a *bug* or a product defect. 
I would also recommend not changing the language in the documentation.

Again, a simple general rule of thumb. When applying a constraint, it will 
only effect the actions that occur after the constraint was applied. If the 
constraint is part of a larger atomic action, then the atomic action will 
fail, if the constraint condition is not met at any time during the atomic 
action. 
(The last part was added to explain why your unique index will fail if there 
are duplicates already in the table. ;-)

But hey, what do I know? ;-)



> 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