"Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please)
wrote in message ...

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
anyone
have discovered with experience, a natural key can change its value under
a
set of various circonstances. One could argue that if a key can change
its
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.