Logical question on primary keys...

[Access rookie]

Hello,

This is a logical question on my understanding of primary keys. If a primary
key is to uniquely identify records in a table and that same key can be used
to tie together various aspects of that same entity, why have two primary
keys?
For example, I have a table that contains bio information, another with
medical history, another with drug plans for that individual, etc. I guess
the wisest thing to do is tie them all together with one key, right?
The way banks operate made me ask this question: if my social security
number uniquely identifies me (and I think they're not duplicated...emphasis
on I think), why do I need an account number?

Thinking like a rookie,

John.

 

[Rick Brandt]

Hello,

This is a logical question on my understanding of primary keys. If a
primary key is to uniquely identify records in a table and that same
key can be used to tie together various aspects of that same entity,
why have two primary keys?
For example, I have a table that contains bio information, another
with medical history, another with drug plans for that individual,
etc. I guess the wisest thing to do is tie them all together with one
key, right?
The way banks operate made me ask this question: if my social security
number uniquely identifies me (and I think they're not
duplicated...emphasis on I think), why do I need an account number?

Thinking like a rookie,

John.

You can have more than one account at the same bank right? So the field that
identifies *you* is not enough. The PK needs to identify the entity that each
row in the table represents.

 

[Access rookie]

Hey Rick,

Thanks for your reply and for putting up with my rookiness. Happy New Year!

John.

 

[Van T. Dinh]

1. You can have more than 2 bank accounts ...

2. An account may be in more than 1 names ...

3. Some people may not have S.S.N. (depending on the country) ...

4. Someone may turn up at the bank ith $10 million deposit and forgets his
/ her S.S.N. The bank obviously doesn't want to turn the potential client
away ...

 

[Tim Ferguson]

If a
primary key is to uniquely identify records in a table and that same
key can be used to tie together various aspects of that same entity,
why have two primary keys?

A table cannot have two primary keys: it may have one PK made up of more
than field; or it may have several candidate keys of which only one must be
Primary.

For example, I have a table that contains bio information, another
with medical history, another with drug plans for that individual,
etc. I guess the wisest thing to do is tie them all together with one
key, right? The way banks operate made me ask this question: if my
social security number uniquely identifies me (and I think they're not
duplicated...emphasis on I think), why do I need an account number?

A medical history table is likely to have several records for each patient;
so it would probably have a PK made up of the PatientID and some kind of
episode identifier, e.g. PresentationDate or FileNumber etc.

DrugPlans is definitely many-to-one.

As I understand it, SSNumbers do not uniquely identify an individual --
there are mistakes and duplicates and people who don't have one at all. And
bank accounts have a complex relationship with people: some people have
lots of accounts; others don't have any at all; and some accounts belong to
institutions rather than people anyway. Then, of course, there are joint
accounts that belong to several people at once.

The golden rules for PKs are: Uniqueness, Stability, and Availability.

Hope that helps


Tim F