primary key

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Valerie - 31 Jan 2004 02:09 GMT

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?

Reply to this Message

Allen Browne - 31 Jan 2004 03:15 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Is there really any reason to use an auto number for the
> primary key if you already have a field that would serve
[quoted text clipped - 11 lines]
> to go. Other materials hardly address it at all. What do
> you experts think?

Reply to this Message

John Vinson - 31 Jan 2004 05:38 GMT

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

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Reply to this Message

Ken Snell - 31 Jan 2004 16:54 GMT

< snip >

> > What do
> >you experts think?
>
> We disagree among ourselves, and sometimes get into shouting matches
> about it <bg>

NO WE DON'T!!

< g >

Signature

Ken Snell
<MS ACCESS MVP

Reply to this Message

Rebecca Riordan - 31 Jan 2004 17:07 GMT

> < snip >
> > > What do
[quoted text clipped - 6 lines]
>
> < g >

DO TOO!!!

Signature

Rebecca Riordan, MVP

Reply to this Message