Re: Surrogate Key vs Production Key

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

>
> Good lord man you aren't using postal codes as keys are you?
> Well of course not ... so what is the point of your example?

What is the point of other examples given here of how NOT to use a surrogate key? Everyone that uses them knows what not to do. Unless they are newbies. Guess what: if they are newbies, they will have a serious problem writing and maintaining the required triggers for RI cascading. 6 of one, half a dozen of the other.

I can't disclose what my clients use, Daniel. I'm sure you know not the full legalities of that. Unlike other countries where people are free to write books about the work they do in clients, over here it is illegal to use anything in public through any media. I have to come up with the nearest example to what they do that is not also what they do. Catch it?

> So lets try this ... how many times can you enter the single
> person Noons in the person table with a surrogate key?

Many times. Unless it is part of a unique key, in which case only once (I hope...). Where do you see a problem with that?

> How
> many times with a natural key? Of course assuming one exists.

Exactly. Assumptions. I had one above too. It all is very much down to them, isn't it?

But in this precise case: there isn't one. You'd need a compound key. Everywhere it is used as a FK, as well. Ibidem for any other concurrently used pks in the RI tree. How much will that overhead grow? For the sake of what? Some theory based on the assertion that "data will be rubbish"? Narh, thanks.

You see, what you folks have failed so far in proving is:

1. that when used as they are supposed to be used and Codd defined them, surrogate keys are inherently unsafe. You can't even define that unsafe without classifying users as sloppy or data as rubbish.
2. that a database without support of the full relational model provides for easy and maintainable handling of natural keys and their cascading.
3. that surrogate keys are not BY FAR the most widely used technique. And why. No: comments like "they are all wrong" don't cut it I'm afraid. Check google: it's your friend.