Redundant indexes with multi-column foreign key indexes

I understand that HSQL can use a multi-column index when only the first
column of the index is used in a query. I also understand that HSQL
automatically creates indexes on foreign keys. This can lead to redundant
indexes when creating a multi-column index that uses multiple foreign key
columns. For example, I have the following setup.

CREATE TABLE A (
   id UUID NOT NULL,
   name VARCHAR(45) NOT NULL,
   PRIMARY KEY (id));

CREATE TABLE B (
   id UUID NOT NULL,
   name VARCHAR(45) NOT NULL,
   PRIMARY KEY (id));

CREATE TABLE C (
   id UUID NOT NULL,
   datetime TIMESTAMP NOT NULL,
   a_id UUID NOT NULL,
   b_id UUID NOT NULL,
   PRIMARY KEY (id),
   CONSTRAINT fk_c_a FOREIGN KEY (a_id) REFERENCES A (id),
   CONSTRAINT fk_c_b FOREIGN KEY (b_id) REFERENCES B (id));

This creates an index for each of the foreign keys on C (columns a_id and
b_id). I know for a fact that I query table C using a_id and b_id together
frequently, so I would like to create an index on both columns.

CREATE INDEX c_a_b_idx ON C(a_id, b_id);

But HSQL can also use this new index (c_a_b_idx) when querying only on
a_id. This means the automatically created index (SYS_IDX_blah) for a_id is
redundant, useless, and only taking up space. When I try to drop the
SYS_IDX_blah index, I get errors about lacking privilege or the object is
not found.

How can I eliminate the redundant index? How can I remove the automatically
created index or prevent it from being created?

------------------------------------------------------------------------------
Developer Access Program for Intel Xeon Phi Processors
Access to Intel Xeon Phi processor-based developer platforms.
With one year of Intel Parallel Studio XE.
Training and support from Colfax.
Order your platform today. http://sdm.link/xeonphi

_______________________________________________
Hsqldb-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user