Primary Keys

Most of the reference books recommend autonum primary keys, but the
Access help says that any unique keys will work.

What are the tradeoffs?

I have several tables that have unique fields. Can I use them as
primary keys or should I define an autonum primary key?

One table has information about the 50 states in the US. The table
looks like this:

State Capitol Date Admitted
Alabama Montgomery December 14, 1819
Alaska Juneau January 3, 1959
Arizona Phoenix February 14, 1912

Since the state names are unique, is there any reason not to make that
field the primary key?

Another table has to do with grade school multiplication tables. This
is a simple table something like this:

Factors Answer
1x1 1
2x1 2
2x2 4
3x1 3
3x2 6
3x3 9
...
12x1 12
12x2 24
...
12x12 144

Here again, the Factors field (a text field) is unique. Is there any
reason not to use it as the primary key?

In both cases, the tables are small, so adding another 4-byte field is
no big deal, but I'd like to keep the tables as simple as possible
unless there is some downside.

Thanks

--
Running MS Office 2000 Pro on Win2000

Using a column that is guaranteed to be unique, as in your examples, will
usually work. The one place you would consider using an autonumber is if you
expected to have to change the other key value. I don't expect any states to
change their names any time soon, so it's probably ok there. In your other
example, is there any risk that you might change the values in the factors
field? On the other hand, even if you did need to change something, a
cascading update relationship would still accomodate this.

Bottom line: if you have a candidate field that, by its nature, is
guaranteed to be unique, use it.

Barry


"LurfysMa" wrote:

Most of the reference books recommend autonum primary keys, but the
Access help says that any unique keys will work.

What are the tradeoffs?

I have several tables that have unique fields. Can I use them as
primary keys or should I define an autonum primary key?

One table has information about the 50 states in the US. The table
looks like this:

State Capitol Date Admitted
Alabama Montgomery December 14, 1819
Alaska Juneau January 3, 1959
Arizona Phoenix February 14, 1912

Since the state names are unique, is there any reason not to make that
field the primary key?

Another table has to do with grade school multiplication tables. This
is a simple table something like this:

Factors Answer
1x1 1
2x1 2
2x2 4
3x1 3
3x2 6
3x3 9
...
12x1 12
12x2 24
...
12x12 144

Here again, the Factors field (a text field) is unique. Is there any
reason not to use it as the primary key?

In both cases, the tables are small, so adding another 4-byte field is
no big deal, but I'd like to keep the tables as simple as possible
unless there is some downside.

Thanks

--
Running MS Office 2000 Pro on Win2000

In my opinion, you're asking for trouble if you ever show the user the
primary key or if you might ever want to edit that information. Since
primary keys are normally the way you establish relationships, you don't
want them to ever change once a record has been created. Even though you
may think the key value won't change, typos have been known to happen.
Also, number fields take up less space in the database and primary keys, as
the source of the relationship, are typically repeated over and over in many
tables.

Therefore, I always use autonumbers. Other opinions vary.

HTH;

Amy

"LurfysMa" wrote in message
...
Most of the reference books recommend autonum primary keys, but the
Access help says that any unique keys will work.

What are the tradeoffs?

I have several tables that have unique fields. Can I use them as
primary keys or should I define an autonum primary key?

One table has information about the 50 states in the US. The table
looks like this:

State Capitol Date Admitted
Alabama Montgomery December 14, 1819
Alaska Juneau January 3, 1959
Arizona Phoenix February 14, 1912

Since the state names are unique, is there any reason not to make that
field the primary key?

Another table has to do with grade school multiplication tables. This
is a simple table something like this:

Factors Answer
1x1 1
2x1 2
2x2 4
3x1 3
3x2 6
3x3 9
...
12x1 12
12x2 24
...
12x12 144

Here again, the Factors field (a text field) is unique. Is there any
reason not to use it as the primary key?

In both cases, the tables are small, so adding another 4-byte field is
no big deal, but I'd like to keep the tables as simple as possible
unless there is some downside.

Thanks

--
Running MS Office 2000 Pro on Win2000

Most of the reference books recommend autonum primary keys, but the
Access help says that any unique keys will work.

What are the tradeoffs?

I have several tables that have unique fields. Can I use them as
primary keys or should I define an autonum primary key?

One table has information about the 50 states in the US. The table
looks like this:

State Capitol Date Admitted
Alabama Montgomery December 14, 1819
Alaska Juneau January 3, 1959
Arizona Phoenix February 14, 1912

Since the state names are unique, is there any reason not to make
that field the primary key?

Another table has to do with grade school multiplication tables. This
is a simple table something like this:

Factors Answer
1x1 1
2x1 2
2x2 4
3x1 3
3x2 6
3x3 9
...
12x1 12
12x2 24
...
12x12 144

Here again, the Factors field (a text field) is unique. Is there any
reason not to use it as the primary key?

In both cases, the tables are small, so adding another 4-byte field
is no big deal, but I'd like to keep the tables as simple as possible
unless there is some downside.

Thanks

I'd recommend you to take the time to use your favourite search engine
for the terms like "natural vs surrogate primary key". Such search
will
probably list some of the pros and cons, in addition to hours of fun
;-)

Basically, some favours usage of surrogate keys (Autonumber), others
favours natural keys, which represents "things" having a business
meaning, and which can also be a combination of fields. Some (including
me) will use both, based upon the requirements. For state, I'd probably
use the two letter code.

Just be aware - for some this isn't just a matter of preference, it's
religion to a degree thats close to fanatism.

Just be sure that if you decide upon surrogate key (Autonumber), then
remember that this will not ensure the integrity of your data! It will
only ensure that each record has a unique number. Say in a table where
you have a unique field, but you decide to add an Autonumber field for
primary key, you will need to also add a unique index on the "natural
key" field in addition to the primary key index on the Autonumber
field,
else you'll risk dupes.

--
Roy-Vidar

On Wed, 12 Jul 2006 11:48:27 -0500, "Amy Blankenship"
wrote:

In my opinion, you're asking for trouble if you ever show the user the
primary key

Why is merely showing the user the primary key a problem?

--
Running MS Office 2000 Pro on Win2000

Primary keys build an index and if you are concerned with database size then
two letter abbreviation for the state would be a smaller index.

I have another case for your in that I have to keep training certifications
and occupational examination records on personnel. But they keep changing
departments, names, employee code when migrating to different subcontractors,
etc. I set up an alias table that will have all changes and you can see that
the database records reflect that Jane Doe, now married to Bill Smith, had
hearing exam last year. The data matches paper records. There is a new
alias record for every change and the front/top displays the latest always
with a subform showing the current and all previous aliases.

"LurfysMa" wrote:

Most of the reference books recommend autonum primary keys, but the
Access help says that any unique keys will work.

What are the tradeoffs?

I have several tables that have unique fields. Can I use them as
primary keys or should I define an autonum primary key?

One table has information about the 50 states in the US. The table
looks like this:

State Capitol Date Admitted
Alabama Montgomery December 14, 1819
Alaska Juneau January 3, 1959
Arizona Phoenix February 14, 1912

Since the state names are unique, is there any reason not to make that
field the primary key?

Another table has to do with grade school multiplication tables. This
is a simple table something like this:

Factors Answer
1x1 1
2x1 2
2x2 4
3x1 3
3x2 6
3x3 9
...
12x1 12
12x2 24
...
12x12 144

Here again, the Factors field (a text field) is unique. Is there any
reason not to use it as the primary key?

In both cases, the tables are small, so adding another 4-byte field is
no big deal, but I'd like to keep the tables as simple as possible
unless there is some downside.

Thanks

--
Running MS Office 2000 Pro on Win2000

On Wed, 12 Jul 2006 10:13:02 -0700, KARL DEWEY
wrote:

Primary keys build an index and if you are concerned with database size then
two letter abbreviation for the state would be a smaller index.

Since there are only 50 states, the savings would be negligible even
if we annex Canada one day! ;-)

I was more interested in usage and reliability tradeoffs.

I have another case for your in that I have to keep training certifications
and occupational examination records on personnel. But they keep changing
departments, names, employee code when migrating to different subcontractors,
etc. I set up an alias table that will have all changes and you can see that
the database records reflect that Jane Doe, now married to Bill Smith, had
hearing exam last year. The data matches paper records. There is a new
alias record for every change and the front/top displays the latest always
with a subform showing the current and all previous aliases.

Sounds messy...

--
Running MS Office 2000 Pro on Win2000

Because by user I mean people other than the developer who might be charged
with maintaining your data. When you show something to that type of user,
you lay it open to being changed. Here's a full discussion of the issue
http://www.dbpd.com/vault/9805xtra.htm

"LurfysMa" wrote in message
...
On Wed, 12 Jul 2006 11:48:27 -0500, "Amy Blankenship"
wrote:

In my opinion, you're asking for trouble if you ever show the user the
primary key

Why is merely showing the user the primary key a problem?

--
Running MS Office 2000 Pro on Win2000

"LurfysMa" wrote

Since there are only 50 states, the
savings would be negligible even
if we annex Canada one day! ;-)

Your mileage may vary, but I've never done a database using states where I
did not, sooner or later, need the state abbreviation, as well as other
information. And, just for the record, my much-used and
much-copied-from-database-to-database "state" lookup table is actually a
table of "US states and Canadian provinces" and, any day now, I may have a
client who will need Mexican states and their abbreviations, too.

In any case, since I need them anyway, I index on the state/province
abbreviation which may give a very minute performance advantage -- it
certainly will not be very significant.

Larry Linson
Microsoft Access MVP

Just to play devil's advocate, at least two of the official provincial
abbreviations have changed in Canada in recent memory (Quebec used to be PQ,
and now is QC, Newfoundland and Labrador used to be NF, and now is NL). We
also got a 3rd territory a few years back, but an addition to the table
isn't as bad as a change to the PK.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Larry Linson" wrote in message
...
"LurfysMa" wrote

Since there are only 50 states, the
savings would be negligible even
if we annex Canada one day! ;-)

Your mileage may vary, but I've never done a database using states where I
did not, sooner or later, need the state abbreviation, as well as other
information. And, just for the record, my much-used and
much-copied-from-database-to-database "state" lookup table is actually a
table of "US states and Canadian provinces" and, any day now, I may have a
client who will need Mexican states and their abbreviations, too.

In any case, since I need them anyway, I index on the state/province
abbreviation which may give a very minute performance advantage -- it
certainly will not be very significant.

Larry Linson
Microsoft Access MVP