List:General Discussion« Previous MessageNext Message »
From:Rhino Date:December 21 2005 8:54pm
Subject:Reason for Auto-increment primary keys?
View as plain text  
One technique that I see a lot on this mailing list is people putting 
auto-incremented integer primary keys on their tables.

Maybe I'm just "old school" but I've always thought that you should choose a 
primary key based on data that is actually in the table whenever possible, 
rather than generating a new value out of thin air.

The only exception that comes to mind is things like ID numbers; for 
example, it is better to use an internally-generated integer for an employee 
number than it is to use an employee's name. Even the combination of first 
name and last name is not necessarily unique - I could cite a real life 
example -and, of course, people can change their names. That makes names 
less desireable than a generated value when you are trying to uniquely 
indentify such entities. In such a case, a nice, reasonable short integer is 
easier.

I just found this rather good definition of primary keys at 
http://www.utexas.edu/its/windows/database/datamodeling/dm/keys.html. The 
relevant bit says that a primary key must have:
- a non-null value for each instance of the entity
- a value that is unique for each instance of an entity
- a value that must not change or become null during the life of the each 
instance of the entity

That article makes the same basic remarks about name vs. ID but makes the 
point that it is more commonly the case that table designers will use 
something like a social security number - an _externally_ generated number - 
to distinguish between employees rather than an internally-generated number.

But the trend in this mailing list is toward using generated values as 
primary keys in virtually EVERY table, even when good primary keys can be 
found in the (non-generated) data already existing in the table.

Now, I haven't done anything remotely resembling a quantified analysis so 
maybe I'm wildly exaggerating this trend. But I do seem to recall a lot of 
table descriptions with auto-generated keys and I don't think they were all 
a name vs. ID scenario....

Has anyone else noticed a similar trend?

If this trend is real, it doesn't seem like a very good trend to me. For 
example, if you were keeping track of parts in a warehouse, why would anyone 
make a table that looked like this:
ID (autogenerated PK)     PART_NO    PART_DESCRIPTION
1                                   A01             Widget
2                                    B03            Grapple Grommet
3                                    A02            Snow Shovel
4                                    D11            Whisk
5                                    C04            Duct Tape

when this table is simpler:

PART_NO (PK)   PART_DESCRIPTION
A01                 Widget
B03                Grapple Grommet
A02                Snow Shovel
D11                Whisk
C04                Duct Tape

Would anyone care to convince me that the first version of the table is 
"better" than the second version in some way?

I just want to be sure that no one has come along with some new and 
compelling reason to autogenerate keys when perfectly good keys can be found 
within the data already. I don't mind being "old school" but I don't want to 
be "out to lunch" :-)


Rhino



-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.2/208 - Release Date: 20/12/2005

Thread
Reason for Auto-increment primary keys?Rhino21 Dec
  • Re: Reason for Auto-increment primary keys?Kenneth Wagner21 Dec
    • Re: Reason for Auto-increment primary keys?SGreen21 Dec
      • Re: Reason for Auto-increment primary keys?Rhino21 Dec
        • Re: Reason for Auto-increment primary keys?SGreen22 Dec
      • Re: Reason for Auto-increment primary keys?Kenneth Wagner22 Dec
        • Re: Reason for Auto-increment primary keys?David Griffiths22 Dec
        • Are primary keys essential?James Harvard22 Dec
      • Re: Are primary keys essential?Kenneth Wagner22 Dec
        • Re: Are primary keys essential?James Harvard22 Dec
          • Re: Are primary keys essential?James Harvard22 Dec
      • Re: Are primary keys essential?Kenneth Wagner22 Dec
      • Re: Are primary keys essential?Rhino22 Dec
      • Reporting tools for summary dataC.R.Vegelin22 Dec
        • Re: Reporting tools for summary dataJames Harvard22 Dec
    • Re: Reason for Auto-increment primary keys?Josh Trutwin21 Dec
  • Re: Reason for Auto-increment primary keys?Peter Brawley21 Dec
    • Re: Reason for Auto-increment primary keys?James Harvard22 Dec
  • Re: Reason for Auto-increment primary keys?Rudy Lippan21 Dec
RE: Are primary keys essential?SST - Adelaide)22 Dec