Re: Surrogate Key vs Production Key

xtanto wrote:

> Hi Gurus,
>
> I have a transaction table having unique external document number. It
> can be assured that it is unique. It is a varchar2(30).
> This is a header-detail transaction.
>
> The options is :
> Whether I should use this unique external document table as PK ?
> (means I have also to store this value in Detail table)
> OR
> Should I create an ID based on Sequence as 'Surrogate' PK ?
>
> What is the cost/benefit ?
>
> Thank you for your help,
> xtanto

Joe Celko weighs in on the side of natural keys and so do I.

Assuming you know your data ... if you implement a surrogate key your only method of assuring that a record is not loaded multiple times is to turn around and create a unique constraint with unique index on the natural key. A total waste of CPU and disk space.

There are times and places for surrogates. This does not appear to be one of them.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)