> IMHO, automatically incremented number fields used for primary keys are
> both a blessing and a curse. It is almost always better to use some
> other data that *means something* for a primary key. If there's no
> possible candidate key, *then* maybe an autonumber key is appropriate.
Just wanted to say, I disagree strongly here (also MHO). I see quite a few
benefits and very few drawbacks to using an auto-incrementing field for a
primary key. In fact, the only drawback I can think of would be that it
takes up a little more space per record to add a field used solely to
uniquely identify that record. I can think of several drawbacks to a
non-auto-incrementing primary key though:
1. Less efficient joins. Comparing integers is about as easy as it gets...
text, char, and varchar require string comparisons, while floating point
numbers are not good as keys because of rounding errors.
2. Discourages value changes. A value that "means something" might need to
be modified in some manner. Sure you can define foreign keys with CASCADEs,
but if you are using an auto-increment, you don't need to!
3. No value is guaranteed to be unique (well, when doing an INSERT or
UPDATE... it only gets into the database if it *is* unique) unless all
queries go through a critical section. To the best of my knowledge, the only
way to do this inside the database is to use nextval either implicitly or
explicitly.
The only time I don't use auto-incrementing fields is when I have a
many-to-many join table with two foreign keys that are both
auto-incrementing fields, in which case the primary key is a combination of
those two fields. Other than a bit of extra space, I don't see any reason
not to.
Greg
