Foreign key & User defined Index Difference

Hi,
  I want to ask only the difference between user defined index & the index
that created while defining the foreign key.
  I have two table emp_master & empl_details.The primary key of emp_master
is foreign key for emp_details.When I asign foreign key then it automaticaly
create index on it.But now I want to remove that trigger, instead I want to
create trigger.I externaly create index on that column.
  So, will it make the difference for retrive the data from tables and how it
effects?

Thanking you,

Monalee Bhandge wrote:

>   I want to ask only the difference between user defined index & the
> index that created while defining the foreign key.

There is no difference.

-- 

Depending on your version of InterBase, foreign keys may have a higher 
selectivity. So, the optimizer may choose to do things differently for 
foreign keys vs user defined indexes.

Monalee Bhandge wrote:
> Hi,
>   I want to ask only the difference between user defined index & the
index that created while defining the foreign key.
>   I have two table emp_master & empl_details.The primary key of
emp_master is foreign key for emp_details.When I asign foreign key then it
automaticaly create index on it.But now I want to remove that trigger, instead I
want to create trigger.I externaly create index on that column.
>   So, will it make the difference for retrive the data from tables and how
it effects?
> 
> Thanking you,

Quinn Wildman (CodeGear Developer Support) wrote:

> Depending on your version of InterBase, foreign keys may have a
> higher selectivity. So, the optimizer may choose to do things
> differently for foreign keys vs user defined indexes.

Could you expand on that? How can two identical indexes have different
selectivity just because one was created automatically as part of a
foreign key and one was created with CREATE INDEX?

-- 

The optimizer used to have priority level something like this:

1. keys
2. unique indexes
3. foreign keys
4. indexes with highest selectivity

I belive it is now:

1. keys
2. unique indexes
3. indexes with highest selectivity

Does this explain it?

Bill Todd wrote:
> Quinn Wildman (CodeGear Developer Support) wrote:
> 
>> Depending on your version of InterBase, foreign keys may have a
>> higher selectivity. So, the optimizer may choose to do things
>> differently for foreign keys vs user defined indexes.
> 
> Could you expand on that? How can two identical indexes have different
> selectivity just because one was created automatically as part of a
> foreign key and one was created with CREATE INDEX?