db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mike Matrigali <mikem_...@sbcglobal.net>
Subject Re: Error when running a delete all a 2nd time
Date Tue, 16 Aug 2005 17:19:36 GMT
Just verifying that declaring a primary key in derby is automatically
creates a unique index on that key.  Thus the warning when creating
the second index.  In this case the second index will provide no
benefit and just use up space and slow down the system when it has
to maintain it.

The warning was added because a lot of users tend to run into this
very problem, adding a unique index that matching the primary key
column.

There was some talk in the past of just allowing the index creation,
but not actually creating the second index and just mark it somehow
in the catalogs as a sort of "view" on an existing index.  Then work
would have to be done to add some sort of reference count on the
underlying index so that drop's of the index didn't actually drop
the underlying structure until the last reference went away.

Army wrote:

> 
> Peart, David E. wrote:
> 
>> Anyway, I'm also getting this error on creating indexes, may be it is
>> related.  So I'd like to see if this was resolved then see if the other
>> problem goes away.
>> The problem is:
>>  
>> WARNING 01504: The new index is a duplicate of an existing index:
>> SQL050816084652910.
>>  
>> But its not a duplicate.  It creates this warning on the 2nd index
>> PARGRP.X2FTNRC2
> 
> 
> [ snip ]
> 
>   CREATE UNIQUE
>          INDEX      PARGRP.X2FTNRC2
>          ON         PARGRP.FTNREC2_CATCTRL
>        (FTNREC2_SG         ASC
>             );
> 
> [ snip]
> 
> If I recall correctly, you had a primary key defined on FTNREC2_SG in
> the DDL for your table.  My guess is that the primary key automatically
> creates an index internally, and thus when you try to create the above
> index on the same column in the same table, the result is a "duplicate"
> index.
> 
> That doesn't explain your overall problem, but that perhaps answers your
> question about the duplicate index...?
> 
> Army
> 
> 

Mime
View raw message