Re: Primary Key Best Practices Poll

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Guinness Mann (gmann_at_dublin.com)
Date: 03/12/04 

Date: Thu, 11 Mar 2004 20:14:20 -0700

On Wed, 03 Mar 2004 22:30:04 GMT, "John Baro"
<johnb@NOSPAMmesware.com.au> wrote:
> I believe that when a primary key logically consists of two or more
> columns it should be created as such.

I try very hard to find a natural primary key, but instances where that
is unreasonable are pretty common. Take the classic invoice line-item
example. The only natural key is a combination of all the attributes in
the table.

So other tables that wanted to have a foreign key into the invoice
line-item table would have to replicate the entire tuple in their own
table. Where's the sense in that?

In that case I would do like your friends suggest and put a unique
constraint on the set of all the attributes and add a tuple-identifier
for easy access by related tables.

In fact, any time the natural key gets over about two attributes I'd
probably do that.

There are better unique identifiers than Identity, though. Some kind of
computed value, like a hash, that can be generated from the data ensures
that if you ever have to merge two like tables you won't have to worry
about Identity-collisions...

-- Rick

Relevant Pages

  • Re: How should I generate a primary key?
    ... And I have agreed that there are cases where a pseudokey is useful. ... the business case that it should be VERY HARD to change ... change the value of the primary key which is more problematic in the field ... The generated key is used as a primary key, but the natural key still sits ...
    (comp.databases)
  • Re: should entities in a database be named using singular or plural
    ... It's interesting to compare the rationale for creating a primary key named ... The alternative would be to use a natural key that is part of the data ... or combination of columns that will maintain uniqueness. ... increase I/O demands on the table and its indexes, decreasing database ...
    (comp.databases)
  • Re: Surrogate Keys: an Implementation Issue
    ... Paul Mansour wrote: ... The primary key should indeed be a natural key, if one is available, at ... This super primary key, is then used when populating FKs ... This seems to give all of the advantages of surrogate keys, ...
    (comp.databases.theory)
  • Re: Surrogate Keys: an Implementation Issue
    ... The primary key should indeed be a natural key, if one is available, at ... This super primary key, (SPK) is then used when populating FKs ... This seems to give all of the advantages of surrogate keys, ...
    (comp.databases.theory)
  • Re: Surrogate Keys: an Implementation Issue
    ... The primary key should indeed be a natural key, if one is available, at ... This super primary key, (SPK) is then used when populating FKs ... This seems to give all of the advantages of surrogate keys, ...
    (comp.databases.theory)