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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13100-primary-keys.html
change their names any time soon, so it's probably ok there. In your other
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13100
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
>