Re: Surrogate Key vs Production Key

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?

> 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...

> 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.

> 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!

> 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. Received on Thu Oct 28 2004 - 03:01:36 CDT