Re: Separate PK in Jxn Tbl?

From: David Cressey <>
Date: Mon, 28 Jan 2008 13:56:11 GMT
Message-ID: <%9lnj.5893$cm6.4751_at_trndny05>

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> wrote in message news:uzBiNoRYIHA.4172_at_TK2MSFTNGP02.phx.gbl...

> This is the big problem with natural keys. When the theory was first
> elaborated, the point was that a natural key never change its value. That
> was the point that was making the choice of a natural key on par with the
> use of a surrogate key as the primary key of a table. However, like
> have discovered with experience, a natural key can change its value under
> set of various circonstances. One could argue that if a key can change
> value, than it's not a natural key but as you know, this argument bring
> nothing in regard to help you choosing a natural key.

This is a specific example of a syndrome that I described more generally: the mismanagement of natural keys.

If a key is "natural", it is managed, if at all, beyond the scope of the DBMS. It might be managed at the application layer, or it might be managed by people, or it might indeed be unmanaged data, like sunspots.

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. Received on Mon Jan 28 2008 - 14:56:11 CET

Original text of this message