Assign Two Primary Keys

[yt100]

SOLVED: Hows it goin?

I need to assign a second primary key to a table involving three fields (Student Number, Subject Code and Average). I have to make Student Number and Subject code the primary keys. Any help would be appriciated.

 

[boblarson]

Although I don't suggest using a composite key, you can do so by selecting both fields in the table's design view and then clicking on the key icon on the tool bar. That will create a composite key for you.

 

[yt100]

thanks

that did it
thanks

 

[doco]

Interestingly, as I read this I am setting in training "Advance Access" and during break. A somewhat lengthy discussion broke out about the statement from the instructor "composite keys should NEVER be used in any circumstance." I don't know everything or seen every circumstance; but would be in general agreement composite keys (natural key) should be avoided in favor of the autonumber (artificial key) .

 

[Rabbie]

Autonumber keys take up relatively little space when they are indexed and are much quicker to sort. Composite keys especially if they are text fields can cause a significant performance hit.

 

[The_Doc_Man]

On the other hand, a composite key makes sense in child tables for which the parent contributes one component and a second non-autonumbered field is the other component.

Don't forget that there is a discussion regarding use of meaningful vs. meaningless keys (search the forum for "Meaningless Key") and there are excellent exchanges in that discussion about when each type is proper.

In an arbitrary case, I might agree that a simple autonumber is preferred, but if the key has or will have meaning, you can't always do that.

 

[RoyVidar]

doco,
if your instructor said that, I think you should request a qualified instructor.

If you search on topic, I think you'll find that even most surrogate key fans will agree that composite primary keys are quite OK under some circumstances. See for instance http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,,sid63_gci1054384,00.html.

For some nuances, check out the replies I got when I asked some questions about an article saying the same (composite primary keys are incorrect, and a primary key should always be one field/autonumber) http://groups.google.com/group/microsoft.public.access/browse_frm/thread/c30046ff27f7f658/ that was given in response to a question in one of the MS public newsgroups.

One of the participants quote several books, which in sum, goes far in the direction of claiming that composite primary keys aren't only allowed, but is the preferred or recommended approach for junction tables.

I think one might also deduce that for other tables, the choice of which method to use, isn't a matter of right or wrong, but more a matter of preference, experience and requirements of the system/client/employer.

Real professionals would likely explain that there are two main methods of selecting primary key, that there are some controversy over which to use, but ensure both methods are given attention, and highlight differences. I do that even in introduction courses.