Unique index not visible in DatabaseMetaData

[Mo Maison <mo...@yahoo.fr>]

Hello Derby users,

I detected a strange behaviour, where indices created by uniques
constraints are not reported in DatabaseMedata.getIndexInfo() :

  *getIndexInfo*(String  <http://docs.oracle.com/javase/6/docs/api/java/lang/String.html>  catalog,
               String  <http://docs.oracle.com/javase/6/docs/api/java/lang/String.html>  schema,
               String  <http://docs.oracle.com/javase/6/docs/api/java/lang/String.html>  table,
               boolean unique,
               boolean approximate)


I tested this with the simple table and two indices (one
auto-generated, and one user index) :
   create table APP.T1 ( f1 integer unique, f2 integer )
   create index index_on_f2 on t1(f2)

if called with unique=true, no index is returned.
If called with unique = false, two indices are returned :
   Index : INDEX_ON_F2  NON_UNIQUE=true
   Index : SQL121222192458030  NON_UNIQUE=true
both with column NON_UNIQUE=true, which surprises me.
Is that expected ?
Problem occurs for unnamed and named constraints.
However IIRC I saw also some indices returned by
getIndexInfo(unique = true ) on much complex database,
thus this behaviour does not seem to be always reproductible.

I use Derby client 10.8.2.2 (also 10.6.2.1), open jdk 1.7 and
oracle jdk on 32 and 64bits linux machines.
I have searched into derby bug database, but could not find
any open bug on this topic (3346 is closed).

   M. Maison

Re: Unique index not visible in DatabaseMetaData

[Knut Anders Hatlen <kn...@oracle.com>]

Mo Maison <mo...@yahoo.fr> writes:

> Hello Derby users,
>
> I detected a strange behaviour, where indices created by uniques
> constraints are not reported in DatabaseMedata.getIndexInfo() :
>
>  getIndexInfo(String catalog,
>               String schema,
>               String table,
>               boolean unique,
>               boolean approximate)
>
> I tested this with the simple table and two indices (one
> auto-generated, and one user index) :
>   create table APP.T1 ( f1 integer unique, f2 integer )
>   create index index_on_f2 on t1(f2)
>
> if called with unique=true, no index is returned.
> If called with unique = false, two indices are returned :
>   Index : INDEX_ON_F2  NON_UNIQUE=true
>   Index : SQL121222192458030  NON_UNIQUE=true
> both with column NON_UNIQUE=true, which surprises me.

I think the reason why it is this way, is that Derby uses non-unique
indexes to back UNIQUE constraints on columns that have not been
declared as NOT NULL. Derby's unique indexes don't allow multiple NULL
values, as they are considered duplicates at the storage level. The SQL
standard, on the other hand, allows multiple rows to contain NULL
values, even if the column is declared as UNIQUE.

So in order to support the UNIQUE constraint on the F1 column, Derby
creates a non-unique index to allow multiple NULL values, and it
performs some extra checks at insert/update to prevent duplicate
non-NULL values.

> Is that expected ?

I don't think so. It sounds more reasonable if the meta-data call
interprets "unique" the way it's specified in the SQL standard rather
than how it's viewed deep down in Derby's storage layer.

Might be worth filing a bug report.

Thanks,

-- 
Knut Anders