db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stanley Bradbury <Stan.Bradb...@gmail.com>
Subject Re: Foreign key -- implicit index?
Date Mon, 19 Mar 2007 21:49:09 GMT
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).


Mime
View raw message