db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: Unique index not visible in DatabaseMetaData
Date Thu, 27 Dec 2012 10:57:30 GMT
Mo Maison <momaison@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.


Knut Anders

View raw message