Re: Surrogate Key vs Production Key

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Sat, 09 Oct 2004 12:48:54 -0700
Message-ID: <1097351419.377686@yasure>

Noons wrote:

> Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1097210055.635712_at_yasure>...
>
>

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

>
>
> Joe never had a client walk in and say:
>
> "you know that code of 'A/0001/sdkjskldjklsd-32998'?
> Shewt!: we mistyped it.
> Can you change it to 'A-0001/sdkjskldjklsd/32998'?
> "
>
> and suddenly he realizes that natural key is now duplicated
> in a 6 level hierarchy of tables as a FK in thousands
> of rows. Which means a major UPDATE on all those tables
> with all RI disabled. Count the downtime.
>
> Zero problem with surrogates. I'll take that anytime.
> For the cost of an extra index. Because I still have to
> see a natural key that somewhere, somehow, doesn't get
> mistyped.
>
> Errare humanum est...
> To really stuff things up, you need a natural key.

I can't speak to whether Joe has had that specific experience but I can say that I have. And I've told the client to go pound sand. A record is either unique or it is not. If it is a surrogate key is irrelevant. And if it is not then you've got a world of pain coming and the only questions is when. And any client that asks to have a primary key changed is not going to see a smile on my face unless I'm being paid by the hour.

This is not to say I don't use surrogate keys ... I use them a lot of the time. But never as the first choice.

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

Received on Sat Oct 09 2004 - 14:48:54 CDT