Dan Karp wrote:
Just to make sure, this foreign key declaration
CONSTRAINT fk_mail_item_parent_id FOREIGN KEY (mailbox_id, parent_id)
REFERENCES ${DATABASE_NAME}.mail_item(mailbox_id, id),
implicitly creates an index on (mailbox_id, parent_id), right?
Yes, that is correct. Copied following from the CREATE INDEX page in
Derby's documentation:
Unique, primary key , and foreign key constraints generate indexes
that enforce or "back" the constraint (and are thus sometimes called
backing indexes ).
Is there any way to explicitly assign a name to this backing index? In my case, I want
to force the optimizer to choose the backing index using "-- DERBY-PROPERTIES
index=".
Hi again -
Of course Dan is correct that using "-- DERBY-PROPERTIES constraint= "
is the way to get the optimizer to use the backing index of the key.
Before I noticed this syntax in the manual, I confirmed the following
kludgey approach which will, in effect, provide a name for the backing
index.
Steps:
1) Create the table without the key defined.
2) Create an standard, named index on the foreign key column.
3) Define the Foreign key using the ALTER TABLE statement.
Derby will use the existing index created in step 2 as the backing index
for the foreign key. This name can be used for the optimizer override.
There will be two index names in the data dictionary that point to the
same physical index (same conglomeratenumber).
