Using autonumber key even where not needed ?

[David]

I have many tables that are used to populate drop-downs.
I've been told that the standard is to have an autonumber
key in every table even when the values in these drop-
downs are unique. Now I have to specify 'indexed - no
duplicates' to prevent duplicate values in these columns.
Is this really the way to go, or am I being misled? I
can't see the purpose of an autonumber key if there is
already a column that must be unique that I can make the
primary key.

 

[Douglas J. Steele]

There's no reason to add an autonumber just for the sake of having an
autonumber. If you've got a good primary key already, stick with it. The
only advantage to having an autonumber might be if your key is long: having
it as a foreign key in other tables will take up more space. An autonumber
will take 4 bytes. If your names are significantly longer than 4 characters,
it might be a consideration (although not a major one!)

 

[John Vinson]

I have many tables that are used to populate drop-downs.
I've been told that the standard is to have an autonumber
key in every table even when the values in these drop-
downs are unique. Now I have to specify 'indexed - no
duplicates' to prevent duplicate values in these columns.
Is this really the way to go, or am I being misled? I
can't see the purpose of an autonumber key if there is
already a column that must be unique that I can make the
primary key.

I'm with you. An Autonumber is a convenient "surrogate key" which can
be used when there is no suitable natural key, or when the natural key
is particularly large and cumbersome, but I for one would not consider
it any sort of standard!

I'd suggest in particular that simple lookup tables (for example,
states and provinces, titles like Mr./Mrs./Miss/Ms/Dr./HRH and the
like) should use the looked-up value itself as the PK.