Two primary keys

[Jane-e]

A book I am reading says, "Composite keys are typically used if a table has no single field (with a set of unique values) that can serve as the primary key." I have used Access for a while, but have never quite grasped what kind of situation needs two primary keys. Can anyone shed light on this?
~ Jane

 

[pbaldy]

I have an application that records driver activity daily. A driver can drive many days, and many drivers can drive on a given day, but no driver can have more than one record on a given day. A composite key on driver and date allows that to happen. I could also use a single autonumber key and enforce the other rules with indexes or something, but that is an instance where a composite key might be used.

 

[Beetle]

A junction table would be one example. The classic case for this is student enrollment, where a student can be enrolled in many classes and a class can have many students. This requires a junction table and the composite key for the junction table would typically be StudentID/ClassID.

 

[boblarson]

And just for your learning - your statement about TWO PRIMARY KEYS is incorrect. A table can have only ONE primary key. It can have several candidate keys or it can have a primary key that, as has been said, is a composite key (a primary key made up of more than one piece of data).

One thing I do frequently, just to simplify things if I am going to need to use the PK from that table in another table, is to use an Autonumber (surrogate key) as the primary key and then I place a Multi-Field Index (set to no duplicates) on the multiple fields I need to keep from having duplicates of the combination of them. Then I only need to store the autonumber in the other table instead of having to store all of the columns of data which would have made up a composite key.

 

[Galaxiom]

Some developers are excessively averse to using composite keys at all and add a surrogate key to everything. I have even heard it claimed that all tables should have an autonumbered primary key. This claim is plain wrong.

There are pros and cons to composite natural keys versus a surrogate.

As Bob pointed out the surrogate key avoids the need to store multiple fields in the related tables. On the other hand sometimes the presence of multiple fields in a related table can allow queries to completely avoid the need for a join which would be necessary to return the required information from another table when using surrogate keys.

Maintaining indexes on fields is a significant overhead especially on a large table. Including a multi-field index as well as the surrogate key can be making unnecessary work for Access.

However one must choose the keys carefully. Text fields with more than very few characters are poor candidates for keys as they are slow to process in a join or query criteria. However given a couple of integer fields or an integer and a date (as Paul gave as an example), I would choose the composite key and eschew a surrogate key. I would certainly think very carfully before using a composite key on three fields and very much doubt I would very use one on four.

And finally, there is a very commonly held belief (encouraged by Access messages too) that all tables must have a primary key. It simply is not true. If there are no related tables and no need to individually identify a specific record then no key is required.

I have a table with up to 10 million records. Managing a key on that table without a good reason would be a huge and pointless overhead.

 

[Jane-e]

These are great responses, and I think it fills it the missing gap. Thank you to Galaxiom, Bob, Sean, and Paul.

Bob — the link to the tutorial was perfect!

Is the definition of "surrogate key" an autonumbered field, or any primary key?

It also sounds as if the decision when designing the table is to choose between composite keys or an autonumber primary key with an multi-field index set to no duplicates (when a single primary key or no primary key has been ruled out, of course.) Is that correct?

The info in the four responses is exactly what I was looking for.

Thanks!
~ Jane

 

[gemma-the-husky]

jane - your article wasnt saying you needed two primary keys- what it was saying was that in some cases, a unique key (ie the primary key) could only be obtained by taking the values from several fields, rather than just one.

eg last name AND first name

 

[Galaxiom]

A surrogate key is any key that is does not have a meaning within in the data itself. Keys drawn from the data are called Natural Keys.

A surrogate key does not have to be autonumbered. It can be allocated by a routine written into the program by the developer.