From: GK

To: Editor

Date: 1 Sep 2004

 

Thanks for the great site.

 

This is rather old, but I thought perhaps it might provoke some thought as a weekly quote (I found it at techrepublic.com)

 

2 Probs with Autonum as primary key

I have found 2 big problems with AN as a primary key. First, if you do an append query into your database, and decide that it was a mistake even during execution, autonumber still increments for the almost-added records, but the AN isn't added since you didn't add the records. It causes a gap in the AN sequence. So you can't then use it as a record count for your db (also, makes it difficult for building queries that reference the AN field for a record range if there are gaps in the sequence).Second, if you use AN as the primary key in 2 different databases, EACH ONE starts from 1. If you go to combine these tables later, you have a key conflict. To resolve this, I generate random numbers in Excel (5-9 digits long) and import these as unique IDs. Access will warn you and not import dups if you set this as the primary key before import.

 

I think my point is that despite warning against the use of autonum, which is reasonable advice, creating artificial primary keys in Excel isn't much better. If you're at the point where you have to import a meaningless number to provide uniqueness, you've already lost.

 

Surrogate keys can be valuable, but generating a random number to serve as a surrogate key, and relying on the DBMS to ensure uniqueness upon insert is a little optimistic. I would think that a surrogate key generated functionally from the natural key would be preferable, so it can be regenerated (or, in extreme cases,  perhaps even decomposed into the natural key again.).

 

 

From: Fabian Pascal

To: GK

 

With current products yes. But good implementations of this function are possible. See PRACTICAL ISSUES IN DATABASE MANAGEMENT chapter on keys.

You don't need surrogate keys if you have natural keys. If the latter are complex, you don't want to concatenate them into overloaded keys (see comment on "intelligent keys" in the book). A good key generation function would let you produce keys such that you control their composition, but that's a different matter.

 

 

Posted 10/08/04