ID number and foreign key

[John J.]

I'm about to create a table with a foreign key. It's the Many-table from a
1xM relationship. I recall reading somewhere that it's always good to add a
primary auto-number key to the table. Can someone confirm if this is right
and can someone explain why this is good? I don't see the use of it in this
case.
Thanks,
John

 

[Douglas J. Steele]

You're starting a religious war! <g>

If you've got a perfectly good candidate key in your table, there's no real
advantage to be gained from adding an autonumber field. If, however, you
haven't got a good candidate key, adding an autonumber field gives you an
easy one.

 

[Arvin Meyer [MVP]]

I'm about to create a table with a foreign key. It's the Many-table from a
1xM relationship. I recall reading somewhere that it's always good to add
a primary auto-number key to the table. Can someone confirm if this is
right and can someone explain why this is good? I don't see the use of it
in this case.
Thanks,
John

In addition to Doug's remarks. I'd like to add that on occasion you may want
to use code or a query to do something to a specific row on the many side,
like archive it, or requery the form and go back to the same record. Having
a Primary Key, in addition to the Foreign Key allows you to do that.

The religious war that Doug referred to has to do with whether or not to use
a candidate key or an autonumber. My rule of thumb for using a natural key
is whether or not you get an added benefit. For instance, many candidate
keys are a compound index. It's much easier to refer to a single field,
rather than multiple ones.

Also some values just don't make much sense to use another key. Take state
abbreviations for example. It doesn't make much sense to use an autonumber
instead of NY or NJ, etc.

 

[Pete D.]

I have manuals that use a specific (old tech, the number, a key in old
datafiles, includes information as to type manual, vehicle...) but in my
access datafile it relates to vehicles that may have duplicate registration
numbers. So to make a long story short in some table relationships I use
just the tech manual number but in others I use autonumbers. Bad part is
sometimes I had to have failure to figure out which was best for each
situation. Experience is the best teacher but if you look very carefully at
the posibility of duplicates and make sure it can't happen you probally will
be on a good path. P.S. SSAN, Name, Address, UPC are never going to
promise no duplicates.

 

[Arvin Meyer [MVP]]

I have manuals that use a specific (old tech, the number, a key in old
datafiles, includes information as to type manual, vehicle...) but in my
access datafile it relates to vehicles that may have duplicate registration
numbers. So to make a long story short in some table relationships I use
just the tech manual number but in others I use autonumbers. Bad part is
sometimes I had to have failure to figure out which was best for each
situation. Experience is the best teacher but if you look very carefully
at the posibility of duplicates and make sure it can't happen you probally
will be on a good path. P.S. SSAN, Name, Address, UPC are never going to
promise no duplicates.

There are probably as many as 3 million duplicate Social Security Numbers
due to Identity Theft, fraud, and illegal aliens. It is not inconceivable
that there could be as many as 7 or 8 million. My son once had an auto
accident, and when my insurance company entered the SSN of the person who
hit him, there were 20 hits, all with the same first and last name.

 

[George Hepworth]

My soon-to-be-ex-wife once held a job in which part of her responsibilities
included tracking down, and identifying, union members with duplicate SSN's
to determine who was legitimate and who MIGHT (<grin>) not have been so, so
that benefits got to the right member. I say might not, because, after all,
it could have been a typo, right?

Not all that big a union in terms of membership, either, as I recall.

 

[Albert D. Kallal]

I'm about to create a table with a foreign key. It's the Many-table from a
1xM relationship. I recall reading somewhere that it's always good to add
a primary auto-number key to the table. Can someone confirm if this is
right and can someone explain why this is good? I don't see the use of it
in this case.
Thanks,
John

The issue of foreign keys versus that of natural keys is a different
argument than your question (unfortunately the other posters kind of went
off key on that issue - pun intended ).

Simply put your are asking when you have a child table and it has a field
that relates it back to the parent table. The next question you're asking is
should this child table have a primary key in this table when there Seems
little if any need for such a primary key ?

The answer is absolutely yes and their is several reasons why.

Of course the first and most of these reasons is down the road you
eventually might have another table that needs to be related to this table,
and therefore you'll need a primary key.

Another good reason is any kind of sql update code needs to deal with ONE
row of the child record. Thus, to use any SQL to update an individual row in
that table will require you to be able to resolve to one row, or simply put
you'll need a primary key to do that sql udpate.

Perhaps the most common reason it comes up for me personally is when trying
to retrieve the last of something for a customer. Lets assume that we have a
master table of customers, and then our child table is one of customer
invoices and the invoice date. To get the person's LAST invoice date we
would go:

select ID, CustomerName,
(select top 1 InvoiceDate from tblInvoices
where customer_id = tblCcustomers.ID
order by InvoiceDate desc) as LastInvoicdDate
from tblCustomers

In the above query were simply going to list the ID, the customer name, and
the last invoice date for this customer. The problem with the above is what
happens if we have two invoice dates for that customer on the same day? It
turns out that the above query will now crap out (since that sub query must
return only one record, and we have no way to return actually one record if
we don't have a primary key in the child table). If we have an auto number
primary key to in the child table, and we simply change the above query to
the following were are home free:

select ID, CustomerName,
(select top 1 InvoiceDate from tblInvoices
where customer_id = tblCcustomers.ID
order by InvoiceDate desc, ID desc) as LastInvoicdDate
from tblCustomers

Note the : order by InvoiceDate desc, ID desc

By ordering the child table by descending ID, and including it in the order
by clause, we can now resolve to ONE reocrd that is the last invoice record
for the customer. otherwise, if a customer has to invoice dates on one day,
how are we going to know which one was the last one? (furthermore, we might
not really care which is the last one, but we have the two invoice dates
with the same date, and are submarines in our application will start to fail
in this example). An auto number primary key solves this problem very nicely
for us.

Very simply put, when you start using SQL to update your data tables, or are
using SQL to pull data from a table, you REALLY do need to be able to
resolve to ONE ROW and you useally need a primary key to do that.

So as a general rule, you need a primary key to resolve and define that one
row that child table. Often in many cases you can come up with an natural
key that is a composite of several fields that makes the row unique.
However, in some cases such as my invoice example you might have more than
one invoice on the same day and thus it becomes more and more difficult to
resolve to one row by a natural key. So, adding an auto number primary key
to the child table solves quite a few problems.

I highly recommend as a design philosophy and approach to development that
you do add auto number primary key to child tables, even when it's not so
particularly obvious that you need such a primary key as per your example.

 

[John J.]

Thanks all for great advice!
I'm convinced
John

 

[Pete D.]

Always someone that enters right on key.

The issue of foreign keys versus that of natural keys is a different
argument than your question (unfortunately the other posters kind of went
off key on that issue - pun intended ).

Simply put your are asking when you have a child table and it has a field
that relates it back to the parent table. The next question you're asking
is should this child table have a primary key in this table when there
Seems little if any need for such a primary key ?

The answer is absolutely yes and their is several reasons why.

Of course the first and most of these reasons is down the road you
eventually might have another table that needs to be related to this
table, and therefore you'll need a primary key.

Another good reason is any kind of sql update code needs to deal with ONE
row of the child record. Thus, to use any SQL to update an individual row
in that table will require you to be able to resolve to one row, or simply
put you'll need a primary key to do that sql udpate.

Perhaps the most common reason it comes up for me personally is when
trying to retrieve the last of something for a customer. Lets assume that
we have a master table of customers, and then our child table is one of
customer invoices and the invoice date. To get the person's LAST invoice
date we would go:

select ID, CustomerName,
(select top 1 InvoiceDate from tblInvoices
where customer_id = tblCcustomers.ID
order by InvoiceDate desc) as LastInvoicdDate
from tblCustomers

In the above query were simply going to list the ID, the customer name,
and the last invoice date for this customer. The problem with the above is
what happens if we have two invoice dates for that customer on the same
day? It turns out that the above query will now crap out (since that sub
query must return only one record, and we have no way to return actually
one record if we don't have a primary key in the child table). If we have
an auto number primary key to in the child table, and we simply change the
above query to the following were are home free:

select ID, CustomerName,
(select top 1 InvoiceDate from tblInvoices
where customer_id = tblCcustomers.ID
order by InvoiceDate desc, ID desc) as LastInvoicdDate
from tblCustomers

Note the : order by InvoiceDate desc, ID desc

By ordering the child table by descending ID, and including it in the
order by clause, we can now resolve to ONE reocrd that is the last invoice
record for the customer. otherwise, if a customer has to invoice dates on
one day, how are we going to know which one was the last one?
(furthermore, we might not really care which is the last one, but we have
the two invoice dates with the same date, and are submarines in our
application will start to fail in this example). An auto number primary
key solves this problem very nicely for us.

Very simply put, when you start using SQL to update your data tables, or
are using SQL to pull data from a table, you REALLY do need to be able to
resolve to ONE ROW and you useally need a primary key to do that.

So as a general rule, you need a primary key to resolve and define that
one row that child table. Often in many cases you can come up with an
natural key that is a composite of several fields that makes the row
unique. However, in some cases such as my invoice example you might have
more than one invoice on the same day and thus it becomes more and more
difficult to resolve to one row by a natural key. So, adding an auto
number primary key to the child table solves quite a few problems.

I highly recommend as a design philosophy and approach to development that
you do add auto number primary key to child tables, even when it's not so
particularly obvious that you need such a primary key as per your example.