Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: neutral (herse.apache.org: 32.97.182.144 is neither permitted
 nor denied by domain of Stan.Bradbury@gmail.com)
Message-ID: <45FF0555.1020709@gmail.com>
Date: Mon, 19 Mar 2007 14:49:09 -0700
From: Stanley Bradbury <Stan.Bradbury@gmail.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Re: Foreign key -- implicit index?
References: <1384897708.43121174255006520.JavaMail.root@dogfood.liquidsys.com>
In-Reply-To: 
 <1384897708.43121174255006520.JavaMail.root@dogfood.liquidsys.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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).