Setting foreign key as index

[Mishanya]

What rule should be followed when desiding whether to set a foreign key in
the table as Index or non-Index field? Why doing so at all?

 

[John W. Vinson]

What rule should be followed when desiding whether to set a foreign key in
the table as Index or non-Index field? Why doing so at all?

If you define the relationship in the Relationships window and enforce
referential integrity, Access will automatically create an index for you. You
should let it do so - it cannot enforce RI without it, and queries joining the
tables will be much more efficient.

 

[Mishanya]

Thank You for reply.
May You specify:

Do You mean setting the relationship in the Relationships window and enforcing
referential integrity makes Access change the Index property of foreign key
to Yes, even I've setted it as No while creating a table? What do You mean by
"Access will automatically create an index for you"?

 

[John W. Vinson]

Thank You for reply.
May You specify:

Do You mean setting the relationship in the Relationships window and enforcing
referential integrity makes Access change the Index property of foreign key
to Yes, even I've setted it as No while creating a table?
Yes.

What do You mean by
"Access will automatically create an index for you"?

If there is no index on the foreign key field, then creating a Relationship
and establishing referential integrity will create a nonunique index
automatically. The index may not be visible in the Indexes window in table
design view, but it will be there, and can be seen in the Indexes collection
in VBA.

 

[Mishanya]

Thank You!

 

[Ken Sheridan]

If I might be allowed to add one thing to what John has already said:

There are some situations where the relationship between tables will be
on-to-one , not one to many. This is where one table represents a sub-type
of the other table. You might for instance have a table Employees, with
columns common to all employees and two tables representing sub-types,
SalesStaff, ProductionStaff, each with columns relevant only to that
particular type of employee.

In this situation EmployeeID would be the primary key in all three tables,
but would also be a foreign key in both SalesStaff and ProductionStaff. In
all three tables the EmployeeID column would be indexed uniquely, which is
automatically done if is set as the primary key of course.

Arising from this, while the EmployeeID column in Employees can be an
autonumber, in SalesStaff and ProductionStaff it can't be, but should be a
straightforward long integer number data type.

Ken Sheridan
Stafford, England

 

[Jerry Whittle]

I'll add another comment to your great question and the great replies that it
received. Access has a very bad habit of creating extra indexes
automatically. By default Access will create a non-unique indexes on field
names that contain any of the following text: ID; key; code; num.

Say that a primary key is named CustomerID. Making it the PK automatically
creates an index PLUS the name having ID in it will create another index.
This second index takes up space and CPU time when a record is added or
deleted. Look for these extra indexes and delete them.

Also you may want to go into Options and modify the AutoIndex feature.

 

[Mishanya]

Jerry, Ken and John, Thank You for kind replies.

 

[Cheese_whiz]

Hi Jerry,

I hate to butt in with a related question, but if you have a junction table
and you have two indexes in the indexes table on the same field (one on the
field by itself, and one on the field as it is part of the primary key),
would you delete the index on the field by itself (read: not part of the pk)?

Just to be clear, I'm talking about a junction table with a primary key made
up of the primary keys from the tables it was meant to resolve.

Thanks,
CW

 

[Jerry Whittle]

Interesting question! I would err on the side of having too many indexes. If
I was really interested in which index was being used or not, I'd use
ShowPlan to analyze index usage. Here's some information on ShowPlan:

http://articles.techrepublic.com.com/5100-10878_11-5064388.html

 

[Cheese_whiz]

Thanks for the reply, Jerry.

I'll check out ShowPlan.

CW