When a primary key reference
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)?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Still have to deal with the separate fields though.....
Scott C
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.
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.
"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 ?
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 ?
<<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.
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.
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
"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
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.
ASKER
Simple presentation, and accurate.
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