http://www.sqlite.org/foreignkeys.html#fk_indexes
Question on making indexes for the child fields of foreign keys. I have a child table with a number of foreign keys on fields which the majority of the time are null. I've currently got indexes on the child fields for the purposes of speeding up the foreign key checks, but what I'm wondering is if I used a conditional index which has "where fkField is not null", will that index be usable by the internal foreign key checker? I'd like to avoid having 10 indexes each of which is 90% full of nulls for example. create table parentTable ( id integer primary key, restOfFields ); create table childTable ( id integer primary key, fkField int references parentTable, --usually null restOfFields --includes many more foreign keys ); create index childIndex1 on childTable (fkField);--normal fk child index create index alternateChildIndex1 on childTable (fkField) where fkField is not null; --will this be used, and help cut down on wasted space? _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users