Questions on Choosing Primary keys (1 Viewer)

andreohnona

New member
Local time
Today, 09:07
Joined
Feb 10, 2006
Messages
8
Hi,

Two questions regarding choosing & using primary keys:

1) Is it always recommended to use Autonumber as primary key? What about a scenario where the table already has an inherently unique identifier, such as a part number in a product table? What about junction tables?

2) In scenarios where I have a "composite" primary key, such as in junction tables for many-to-many relationships, how do I relate that table to another table?(i.e. to one that it is not already related to as part of the many-to-many)

I am interested in understanding this stuff so all explanations are much appreciated...

thanks!

Andre
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 08:07
Joined
Feb 28, 2001
Messages
27,001
1) Is it always recommended to use Autonumber as primary key?

No.

What about a scenario where the table already has an inherently unique identifier, such as a part number in a product table?

This is an excellent example of a case where Autonumber is not recommended.

What about junction tables?

What about them? Junction tables can have auto-number prime keys or can have a compound prime key based on the combination of individual keys. It just depends on whether the combination is unique or common. For unique junctions (combination either exists once or not at all), the set of all foreign keys that define the junction can be a compound prime key. Where the combinations are not unique, some authorities do not require that the junction table have ANY prime keys. (Access wants one because it is too stupid to know better...)

In scenarios where I have a "composite" primary key, such as in junction tables for many-to-many relationships, how do I relate that table to another table?(i.e. to one that it is not already related to as part of the many-to-many)

I'm not sure how you mean the question. If you are relating the junction table to another table, you need to decide whether the junction table has a prime key or just a dups-allowed key because it makes a difference.

If the junction combination is unique and the keys are short, the hypothetical third table might just be merged into the junction table. On the other hand, if there is an item in the junction (not related to the tables participating in the junction) that makes it unique in combination with the foreign keys, you can either include the field that makes it unique OR you can assign an autonumber and use that to link to the hypothetical extra table.

I can see a dated sales invoice as an example of the latter - where you need an invoice number or a date to tell the difference between two different cases where you sell the same part number to the same customer on two different days.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:07
Joined
Feb 19, 2002
Messages
42,981
The primary key of a junction table is most commonly a two-field key. It doesn't make any sense to allow duplicate relation rows. If the relation may exist multiple times such as in the case of student/class, then you need to include a third field in the primary key which in this case would be semester. A student may take the same class multiple times but not in the same semester.

I would only use an autonumber as the pk for a juncton table if that table had a child table and in that case, what would be considered the "Natural" key must be used as a unique index to enforce business rules against duplicates. Multi-field relations are a problem if you ever need to use a combo or list box. That's why in this case I use autonumbers.
 

andreohnona

New member
Local time
Today, 09:07
Joined
Feb 10, 2006
Messages
8
Doc Man & Pat,

Thanks a lot for the explanations. Definitely helps!

regards,

Andre
 

Users who are viewing this thread

Top Bottom