The following set of base tables, join indexes, queries, and EXPLAIN reports demonstrate how the referential integrity relationships among the underlying base tables in a join index definition influence whether the Optimizer selects the index for queries that reference fewer base tables than are referenced by the join index.
CREATE SET TABLE t1, NO BEFORE JOURNAL, NO AFTER JOURNAL ( x1 INTEGER NOT NULL, a1 INTEGER NOT NULL, b1 INTEGER NOT NULL, c1 INTEGER NOT NULL, d1 INTEGER NOT NULL, e1 INTEGER NOT NULL, f1 INTEGER NOT NULL, g1 INTEGER NOT NULL, h1 INTEGER NOT NULL, i1 INTEGER NOT NULL, j1 INTEGER NOT NULL, k1 INTEGER NOT NULL, CONSTRAINT ri1 FOREIGN KEY (a1, b1, c1) REFERENCES t2 (a2,b2,c2), CONSTRAINT ri2 FOREIGN KEY (d1) REFERENCES t3(d3), CONSTRAINT ri3 FOREIGN KEY (e1,f1) REFERENCES t4 (e4,f4), CONSTRAINT ri4 FOREIGN KEY (g1,h1,i1,j1) REFERENCES t5(g5,h5,i5,j5), CONSTRAINT ri5 FOREIGN KEY (k1) REFERENCES t6(k6)); CREATE SET TABLE t2, NO BEFORE JOURNAL, NO AFTER JOURNAL ( a2 INTEGER NOT NULL, b2 INTEGER NOT NULL, c2 INTEGER NOT NULL, x2 INTEGER) UNIQUE PRIMARY INDEX(a2, b2, c2); CREATE SET TABLE t3, NO BEFORE JOURNAL, NO AFTER JOURNAL ( d3 INTEGER NOT NULL, x3 INTEGER) UNIQUE PRIMARY INDEX(d3); CREATE SET TABLE t4, NO BEFORE JOURNAL, NO AFTER JOURNAL ( e4 INTEGER NOT NULL, f4 INTEGER NOT NULL, x4 INTEGER) UNIQUE PRIMARY INDEX(e4, f4); CREATE SET TABLE t5, NO BEFORE JOURNAL, NO AFTER JOURNAL ( g5 INTEGER NOT NULL, h5 INTEGER NOT NULL, i5 INTEGER NOT NULL, j5 INTEGER NOT NULL, x5 INTEGER) UNIQUE PRIMARY INDEX(g5, h5, i5, j5); CREATE SET TABLE t6, NO BEFORE JOURNAL, NO AFTER JOURNAL ( k6 INTEGER not null, x6 INTEGER) UNIQUE PRIMARY INDEX(k6);