Re: Is the use of VARCHAR(256) as Primary Keys preferred in Oracle?

On Mon, 18 Nov 2002 07:35:59 -0800, Jim <no-spam_at_no-spam.org> wrote:

>I confess I don't understand the preference for the use of a surrogate
>key instead of a natural key except in special cases.
>
>I can see that a surrogate, "artificial" key would have some value IF
>all "natural" columns in a table must be updatable, IF no 'natural'
>column, or reasonable combination of 'natural' columns, would be
>unique, and IF all updating of the table would be perfomed by
>application programs able to deal with the essentially meaningless
>surrogate key.
>
>On the other hand, use of a 'natural' key composed of column(s)
>understood and meaningful to the user would, it seems to me, yield a
>more straightforward, easier for the user to understand design.
>
>Am I missing something (probably)?
>
>

The meaning of such a natural key tends to evolve over time, especially when users didn't specify their requirements correctly during the initial design phase. Quite often you end up with adding extra (quite often meaningless ordinal ) columns to those key, and inevitably all those columns would have to be replicated to any other tables referring to them.
This results in designs with primary keys composed of up to 4 or 5 columns, do you still think that is 'meaningful'? It definitely hampers performance, so one should better use surrogate keys from the start.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Mon Nov 18 2002 - 11:32:05 CST