primary key

[Valerie]

Is there really any reason to use an auto number for the
primary key if you already have a field that would serve
adequately as a primary key?

I understand there is a DLookup function that I could use
to specify a starting number, and I would like to know
how to do that for some of my tables. For another, I'm
told I can't use the DLookup feature because my primary
key field has dashes in it.

But if there is a good reason to use an auto number for
the primary key, that's what I'll do. Some of the
literature I have leads me to believe that's the only way
to go. Other materials hardly address it at all. What do
you experts think?

 

[Allen Browne]

The AutoNumber if very convenient, but it is not the only kind of primary
key. For example, I often use text-based primary keys for small look up
tables, e.g. where the category name is the unique identifier anyway. The
only down-side is that you must remember to use extra quote marks in
expression for this field, e.g. in the 3rd argument of DLookup().

If the name of your primary key field is giving you problems, surround the
name with square brackeets.

Including the hypen character in the data of a primary key field is not a
good idea, since the results are not consistent across different version of
Access. More info in these articles:
http://support.microsoft.com/?id=236952
http://support.microsoft.com/?id=271661

 

[John Vinson]

Is there really any reason to use an auto number for the
primary key if you already have a field that would serve
adequately as a primary key?

In my opinion, no there isn't. If you have a good Natural Key, by all
means use it!

The criteria for a good key are that it is unique (you will NEVER have
the same value twice); stable (field whose value changes over time can
be handled but at considerable cost in complexity); and preferably
short (a 255-byte Text primary key is possible, but cascading it into
a bunch of related tables will waste a lot of space.)

I understand there is a DLookup function that I could use
to specify a starting number, and I would like to know
how to do that for some of my tables. For another, I'm
told I can't use the DLookup feature because my primary
key field has dashes in it.

Ummm... not true at all. DLookUp can look up any datatype, text,
numeric, or date.

However, if you're talking about *incrementing* a text field, that
gets a bit more complex. What's the format of your natural key?

But if there is a good reason to use an auto number for
the primary key, that's what I'll do. Some of the
literature I have leads me to believe that's the only way
to go. Other materials hardly address it at all. What do
you experts think?

We disagree among ourselves, and sometimes get into shouting matches
about it <bg>

 

[Ken Snell]

We disagree among ourselves, and sometimes get into shouting matches
about it <bg>

NO WE DON'T!!

< g >

 

[Rebecca Riordan]

NO WE DON'T!!

< g >

DO TOO!!!