Mo Maison <momai...@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
