Re: Foreign key - index

Josh Kupershmidt wrote:
> On Sat, Dec 25, 2010 at 12:33 PM, Majid Azimi <majid.merkava@gmail.com> wrote:
>
>> hi guys.
>>
>> Does adding a foriegn key, create an implicit index like adding unique and
>> primary keys? or we should do it manually.
>>
>
> Creating a foreign key constraint will not create any additional
> indexes on either the target table or the referencing table.
>
> Josh
>
>
Although it is a smart decision to create index on the foreign key
column in the child table, because it is conceivable that an application
will require a join between the parent and child tables sooner or later.
Foreign key constraint essentially means that there is an one-to-many
relationship between the columns of the parent and child tables and a
mere existence of this relationship is a sufficient reason to suspect a
join to be attempted sooner or later. Foreign key constraint references
a unique key in the parent table, so the index in the parent table
exists by default.  An index on the client column will be needed for the
good performance of such join. Joins usually look like this:

select <something>
from parent p join child c on (p.col1=c.col2)
where p.col1=$VAR

When creating an ER model in which there is a relationship CHILD >>--->
PARENT, it is smart to index the child side, too. Of course, there is
nothing to compel the designer to do so, except reason.

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com