Dear John:
Consider the Categories table in Northwind. The table has two columns, a
autonumber primary key and a Category column. There is no uniqueness on
category. You could enter the same category 10 times. Each would have
adifferent autonumber identity, and any items assigned to the categories by
the autonumbered relationship would be indistinct if they belong to the same
named category.
This is a perfect example of the fact that it is very wrong to fail to
guarantee uniqueness of the natural key.
Indeed, I would suggest that the natural key Category column should be the
primary key, and that if you wish to use an autonumber for related tables,
this should be a separate unique index. The reason is this. For a Jet
database, when you Compact and Repair, the rows in the Category table will
be arranged in ascending primary key order. Putting them in alphabetical
order, such as would commonly be displayed on the screen or in a report,
would give a modest performance advantage. Putting them in autonumber order
would require more random hard drive activity to read them in alphabetical
sequence (not for a very small number of rows, but if there ever were many
rows this would be a definite factor).
For a SQL Server database, the clustered index should always be on a natural
key index. Clustering by a fairly random value like the identity would not
be helpful. When would you ever request the rows in a table in identity
value order?
When the rows in a table are physically ordered, the benefit of this will be
seen if you order them in some fashion so that you will actually be using
the rows in that order. Ordering them by an autonumber/identity would be a
fairly random ordering (especially if your autonumber/identity is being
generated randomly) and would not seem to be something that would be
beneficial.
As a best practice, if you use autonumber/identity values for your
relationships, don't make them Primary Keys or Clustered. Keep that for the
natural key values. It is very sufficient to your purpose to just make them
a separate unique index. That generates an index that can certainly be used
in functioning JOINs for the relationship, and the uniqueness will identify
the foreign table as being on the 1 side of any relationship.
Any comment on this, John, or others?
Tom Ellison
Access MVP
