We help IT Professionals succeed at work.

When a primary key reference

SowleMan
SowleMan asked
on
Medium Priority
447 Views
Last Modified: 2013-11-27
When a primary key consists of multiple fields, is there a way to refer to it as a single entity (rather than the component fields)?
Comment
Watch Question

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
No.
And there is really no reason to have a multi-field PK.  Add an AutoNumber for the PK.
Then, if desired ... create a Compound Index on those three fields, and set the Unique property to Yes.
You accomplish the same thing, and you can now reference a single field name.  And ... it will be a LOT easier to create all of your Relationships.

mx
Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
It's rare, but the only time you would use a multi-key PK is if you have multiple fields that cannot have duplicate across all the fields.  For instance, you can have multiple DAYS = MONDAY, and you can have multiple USERS = SCOTT, but you can't have multiple DAYS = MONDAY AND USER = SCOTT.

Still have to deal with the separate fields though.....

Scott C
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:

 Yes, many to many linking tables is about the only place where you'd want to use a composite key.

  With the computer systems we have today, natural keys just don't work well performance wise.

JimD.
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:

 BTW, if you want to read a little more about keys in general, there are a few articles here on EE including one I wrote:

https://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2041-The-great-PK-debate-Natural-Keys-vs-Surrogates-again.html

JimD.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
"It's rare, but the only time you would use a multi-key PK is if you have multiple fields that cannot have duplicate across all the fields. "
Not the case.  As I showed, you can create a Unique Compound index on those fields to accomplish that.

" Yes, many to many linking tables is about the only place where you'd want to use a composite key."
Why ?
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:
<<Why ?>>

 Performance.  With today's systems, anytime your going to join on a table, your typically add an autonumber as the key because using anything else as a FK in another table leads to performance problems. With a simple many to many linking table though, it doesn't make sense to add one as you would never form a relationship to it.

JimD.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
CERTIFIED EXPERT
Top Expert 2007

Commented:
Are we saying the same thing, but I think not based on:
"With a simple many to many linking table though, it doesn't make sense to add one as you would never form a relationship to it."
Maybe not, but ... IF ... you need to refer to a record in the M2M ... then you only need us U12M2MID (example below).  But, I could go either way on this one.

mx
Capture1.gif
Capture2.gif
Jim Dettman (EE MVE)President / Owner
CERTIFIED EXPERT
Fellow
Most Valuable Expert 2017

Commented:

 Yes, I would only add U12M2MID if I was going to use the key in another table to form a relationship.  Otherwise, I'd use the composite key of U1ID and U2ID.

JimD.
SowleManRetired

Author

Commented:
Simple presentation, and accurate.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.