Multiple Field Keys, Why, When, Where and How?

[Jim Strenk]

Many examples of table design show one field as the primary key to be used
as a foreign key in a different table such as StudentID in Table Students,
SubjectID in Table Subjects and StudentID and SubjectID in Table Grades.

When is it desireable to have the primary key of a table contain more than
one index? Can someone give me examples when it is required, and when it
would not be a good thing to do? What is the purpose of multiple index key?

[Jeff Boyce]

Jim

We might have a slight disconnect re: terminology.

A table (can have) a (single) primary key. A primary key is indexed, with
no duplicates allowed.

Indexing is a technique that makes it easier to find something. In a text
book, an index helps you find where a word or phrase is located faster than
reading each word. In Access, an index helps Access find rows faster.

An index does not have to have "no duplicates" -- duplicates can be allowed
(but not for a primary key).

An index can consist of more than one field in the table. You can have more
than one index on a table.

One example of a table with a primary key and multiple indexes might be an
Address table. I typically build a tblAddress with an Autonumber Primary
Key, a unique, arbitrary (i.e., non-meaningful) long number. I might index
the City field, to make Access run faster when I'm looking for addresses in
a particular City. I might also index the StateProvince field, to make it
faster to find addresses in a particular State. If my address table also
has a field that holds the initials/name of the person who entered the
address, I might index that, too, as a way of more quickly finding all the
addresses I entered.

Good luck

Jeff Boyce
<Access MVP>

[TomG]

To expand a bit on Jeff's comments, a Primary Key may, like any index,
consist of more that one field. However I suggest avoiding that if possible.
Better to use a meaningless autonumber instead. The reason is that if you
use a Primary Key with multiple fields, then all those fields must also be
used as foreign keys in any table to which you want to establish a
relationship. This can become rather akward.
--

~~~~~~~~~~~~~
Make a Great Day
TomG

"Jim Strenk" <jims...@mindspring.com> wrote in message
news:KiSU8.40$pI1...@newstest450b.news.pas.earthlink.net...