?
Solved

When a primary key reference

Posted on 2011-04-21
10
Medium Priority
?
436 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)?
0
Comment
Question by:SowleMan
10 Comments
 
LVL 75
ID: 35445923
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
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1000 total points
ID: 35445948
see image
Capture1.gif
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 35446948
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58
ID: 35447569

 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.
0
 
LVL 58
ID: 35447582

 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:

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

JimD.
0
 
LVL 75
ID: 35448594
"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 ?
0
 
LVL 58
ID: 35448829
<<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.
0
 
LVL 75
ID: 35448904
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
0
 
LVL 58
ID: 35449376

 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.
0
 

Author Closing Comment

by:SowleMan
ID: 35451885
Simple presentation, and accurate.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question