Martin:

I changed muy mind, here's my response.

> Yup... However, I don't like holy truths like thou shall always use
> surrogate keys and I wanted to point out that a natural key can be a
> good choice in some instances.

> Here we go again.
>
> Just tell me an example. I guess I can find a situation in which it can
> becaome a problem.
>
> Maybe some of you can give me an example in which having a surrogate key
> would cause trouble, indeed.

For good natural key choices (US State abbreviations, DOT Codes), read my response to George Tasker in the Color of Disabled – Gray thread.

The following is an example of surrogate keys that can cause problems. In one of our applications, we have a table that has around 200 fields, about 80 of which are codes that are primary keys in lookup tables (these codes carry well understood meaning and none of them has changed in at least 10 years; furthermore, it is very unlikely that any will change in the near and not so near future. In all your posts I have read so far, you say I should use surrogate keys because (1) it removes the need of a cascade update, and (2) consistency. In response to (1): a code value change is considered so unlikely (it would require a lot of effort from many organizations) that we consider the lack of a native cascade update a non-issue. In response to (2): sure we could add surrogate keys and add an additional 80 tables to the join condition when we want to print our main report…

IMO, the proponents of pure surrogate keys put too much emphasis on an issue (Cascade Update) that may be a non-issue and throw the consistency buzzword around to put more credibility on their statement. I don’t think the consistency label does not add any additional credibility: consistent can be used with both bad or good and any rule that is consistently followed blindly is bad IMO :-).

Daniel