Re: Basic Q on superfluous primary keys

From: Greg Smith <gsmith(at)gregsmith(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Basic Q on superfluous primary keys
Date: 2007-04-17 03:02:48
Message-ID: Pine.GSO.4.64.0704162239300.7058@westnet.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Mon, 16 Apr 2007, Merlin Moncure wrote:

> extraordinary cases do happen, like a company overhauling its numbering
> systems, but such cases can be dealt with by a number of methods
> including letting RI do its thing.

I think the point Craig was trying to make is that what you refer to here
as "extraordinary cases" are, in fact, rather common. I've never seen a
database built on natural keys that didn't at some point turn ugly when
some internal or external business need suddenly invalidated the believed
uniqueness of that key.

The last really bad one I saw was a manufacturing database that used a
combination of the customer code and the customer's part number as the
key. Surely if the customer changes their part number, we should switch
ours to match so the orders are easy to process, right? When this got fun
was when one large customer who released products on a yearly schedule
decided to change the bill of material for many of their parts for the new
year, but re-used the same part number; oh, and they were still ordering
the old parts as well. Hilarity ensued.

> it is especially unclear how adding an integer to a table will somehow
> magically solve these problems.

If the key is a integer, it's always possible to figure out a trivial map
that renumbers the entire database programmatically in order to merge two
sets of data.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Robins Tharakan 2007-04-17 03:24:15 Fwd: Strangely Variable Query Performance
Previous Message Tom Lane 2007-04-16 23:55:06 Re: FK triggers misused?