Primary Keys

[sadiamalik1980]

How do you create a primary key from multiple fields in a table?
Regards

 

[Beetle]

Open the table in design view, hold own the control key while you
select the necessary fields, then click the Primary Key button on the
toolbar or go to Edit/Primary Key.

If you have A2007 it will probably be slightly different (i.e. you'll
need to select the design tab on the ribbon or something - don't
have A2007 here).

 

[John W. Vinson]

How do you create a primary key from multiple fields in a table?
Regards

Open the table in design view.
Ctrl-leftclick each field which will be in the PK (it will darken to indicate
that it's been selected).
Click the Key icon.

 

[Philip Herlihy]

Open the table in design view.
Ctrl-leftclick each field which will be in the PK (it will darken to indicate
that it's been selected).
Click the Key icon.

I've often wondered what the pros and cons of such a primary key might be?

Phil, London

 

[John Spencer]

OHHH be careful, that question can start a religious war.

Personally I use both. Usually I use an artificial key such as an
autonumber, but in some cases a multi-field primary key makes darn good
sense.

A multi-field primary key in a junction table (many to many
relationship) makes a lot of sense to me. That way I can easily limit
the combination of fields (foreign key fields) to one per each type of
combination. I know I could do the same with a standard multi-field
index, but this case makes sense TO ME.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Philip Herlihy]

OHHH be careful, that question can start a religious war.

Personally I use both. Usually I use an artificial key such as an
autonumber, but in some cases a multi-field primary key makes darn good
sense.

A multi-field primary key in a junction table (many to many
relationship) makes a lot of sense to me. That way I can easily limit
the combination of fields (foreign key fields) to one per each type of
combination. I know I could do the same with a standard multi-field
index, but this case makes sense TO ME.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

That was the only scenario I could think of! I guess it started me
thinking what a primary key really was. Sure, it's what you join on, but
it's also what you describe to the Access system tables so that the
program can alert you to potential duplication. But does it *really*
mean any more than an index where duplicates are disallowed? The phrase
"candidate key" comes to mind. If you have multiple candidate keys, is
any of them more primary than any other? Or does this only come out
clean in fifth normal form (which I only dimly understand, or is it
misunderstand?).

Much appreciate your steadfast patience in answering SO MANY
ill-informed questions!

Best wishes,

Phil