Re: Separate PK in Jxn Tbl?

From: Roy Hann <specially_at_processed.almost.meat>
Date: Mon, 28 Jan 2008 14:30:09 -0000
Message-ID: <>

"David Cressey" <> wrote in message news:%9lnj.5893$cm6.4751_at_trndny05...
> When it is managed by other people, it is subject to mismanagement.
> Changing values that ought to be immutable is one of many ways that
> natural
> keys can be mismanaged.
> As I said before, I prefer to use natural keys where ever possible. If
> that's not possible due to mismanagement of the natural keys, I'll use
> synthetic keys.

I think you may be asking too much of a natural key. I always took "stable" to mean *almost* never changes ratherer than meaning absolutely immutable (which I take to mean "intrinsically incapable of changing"). But whatever one thinks it should mean, one always has to accommodate the possibility that a key value will be mis-typed during manual entry by a user, and therefore that it will have to be corrected. Mismanagement is, as you rightly say, a fact of life.

But that's not usually a sufficiently good reason to introduce a synthetic key. I have found it possible to tolerate quite a lot of updates to supposedly stable keys using ON UPDATE CASCADE, with imperceptible response-time costs. (One can of course measure the costs, but that's not what counts.) A key would have to be really unstable to justify using a synthetic key IMO.

Roy Received on Mon Jan 28 2008 - 15:30:09 CET

Original text of this message