Re: Surrogate Key vs Production Key

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Thu, 28 Oct 2004 17:50:00 -0700
Message-ID: <1099010940.913494@yasure>

Noons wrote:

> DA Morgan <damorgan_at_x.washington.edu> wrote in message news:<1098930023.768375_at_yasure>...
>
>

>>>>And just like with Cobol and Y2K ... hopefully you'll have
>>>>a different job when the duplicates are found and it won't
>>>>be your problem.
>>>
>>>Show me why using surrogate keys stops one from using
>>>a unique key where it is needed?
>>
>>Of course nothing does. And whenever I use surrogate keys I always
>>try to protect my data with a unique constraint.

>
>
> So, why are you suggesting that the use of surrogate
> keys somehow implies loss of uniqueness on other keys?

I hopefully never did. I said that to maintain the integrity of the data one must then create additional constraints. Sorry if my original posts were unclear. I often type these missives after a full 10 hour work day followed by 3 hours of lecture.

>>But lets acknowledge we are talking about two more constraints (unique 
>>and not null) plus one more index. Each with its attendant overhead. 

>
> I'll trade that ANYTIME for the overhead of a long character
> PK that I have to index as well (possibly a composite index)
> and update wherever it is used as a FK whenever someone
> decides that "john"||"smith" is really "John"||"Smith".
> And that was a simple case...

Your examples to me don't qualify as natural keys so I would, based on them agree with you. But if your natural key example had been a driver's license number or an ISBN, or a postal code I would disagree.

>>More not null check constraints if it is the unique index is multicolumn

>
> It can't be. Or else you aren't using a surrogate key.

I am refering to your unique index to maintain data integrity. It will allow NULLs if you don't also add check constraints.

>>and you still have the original issue that if you were to merge your
>>data with other data ... the surrogate keys will be duplicated in the
>>other system.

>
>
> Not at all. But "John"||"Smith" sure would be!

John Smith, even by the wildest of imaginations is not a natural key. Try your example again with a real one such as an ISBN or Postal Code.

>>Understand, please, I use surrogate keys daily. 

>
> Doesn't look like it. You keep bringing in arguments that simply
> do not apply to surrogate keys or are not exclusive to their use.

Look more closely. ;-)

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

Received on Thu Oct 28 2004 - 19:50:00 CDT