Re: What are cons and pros for using IDENTITY property as PK in SQL SERVER 2000?

"Louis Davidson" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> "Bob Badour" <[EMAIL PROTECTED]> wrote in message
> news:[EMAIL PROTECTED]
> >
> > Then I can only conclude you lack the ability to comprehend relatively
> > simple written english.
>
> Well, then apparently you are so good at English, and less good at being
> precise.  If natural keys and surrogate keys were in fact the same thing,
> then why would we have multiple terms for these things?

I don't recall saying they are the same thing. I recall saying that one is a
subset of the other. Perhaps, if you had better grasp of written english,
you would have observed that the first time. I see nothing imprecise about
what I said.


> >That might be a good definition of an IDENTITY column, but it has no
> bearing
> >on surrogate keys. By equating the identity columns with surrogate keys,
> you
> >only confuse yourself and potentially any similarly uneducated readers.
> >Nothing about a surrogate key requires a DBMS to generate it.
>
> I did not come up with any of this terminology.

That's obvious. You do not comprehend the terminology either.


> An identity is an
> artificial key.

It is an rdbms generated key.


> It is used as a surrogate key.

All keys are surrogates.


> It is not a natural key.

While the key remains unfamiliar, this is true. However, as soon as it
becomes familiar, this ceases to be the case. I was assigned an arbitrary
nine digit university identification number two degades ago. I can still
recite this number easily having used it on literally hundreds if not
thousands of pieces of correspondence. That sounds like a natural enough key
to me.


> A
> surrogate key is not required to be created by the DBMS...

Well, then, let's omit that requirement from your earlier definition and see
whether natural keys are surrogates:

"A surrogate key (the word surrogate meaning to take the place of, and the
definition of a surrogate key is: A unique primary key that is not derived
from any [other] data in the database and whose only significance is to act
as the primary key."

I suggest we could replace "the primary key" with "an identifying attribute"
without any loss of meaning and probably gain some clarity. Cleaning things
up a little:

A surrogate key is a unique identifying attribute that is not derived from
any other data in the database and whose only significance is to act as an
identifying attribute.

Within many contexts, my name is a unique identifying attribute that is not
derived from any other data and whose only significance is to act as an
identifying attribute within those contexts.

Does that not make my name a surrogate key within those contexts?

My name does not suffice within other contexts. Therefore, I have the nine
digit university identification number I mentioned above and a driver's
license number and a social insurance number and a social security number
and a couple of credit card numbers and a whole bunch of bank account
numbers.

Other than familiarity, what distinguishes a natural key like my name from
any other surrogate?


> >I am not my name and my name is not me. My name is a surrogate for me
> chosen
> >for simplicity (simple for an english speaker to say), familiarity
> (familiar
> >for english speakers) and stability (my name changes rarely).
>
> You name is part of what makes you you, because everyone has a name.

Your statement shows a general lack of imagination. Not every infant is
named at the moment of birth, and my name is not a part of me. It is
external to me, and I do not change when my name changes.


> >> Why?  Do you want the user typing, remembering, or dealing with the
> >> difference between ID=320983902 and 320984902 or 320983903?
>
> >That depends. If I were creating an identifier for a credit card, I would
> >want at least seven more digits, and yes I would want users typing,
> >remembering, swiping and dealing with the numbers.
>
> Strangely enough, this is done with credit cards specifically so it will
be
> hard to rememeber.  On the other hand, I would not use a credit card
number
> as the primary key of a credit card table.  Why?  Because I would not want
> that key spread around the database in other tables.  A surrogate key here
> would be used to obscure that fact, leaving only a single point of contact
> with credit card numbers to be secured.

If you worked for a credit card company, you would see things differently.
After all, the credit card number is an account number for the credit card
company. Their whole business relates to those accounts and they use those
numbers to identify the accounts to the external world including to business
partners and to credit bureaus.


> >Preventing the user of the data from seeing the identifier for the data
is
> >just plain stupid.
>
> If you don't see my point, and
> I clearly cannot see your point through all of the muck and anger, then
why
> waste time.

Again, I suggest your perception of anger suggests your ability to
comprehend written english sorely lacks. I invite you to consider whether
you project your own emotional state onto the words you read and to consider
whether this might cloud your ability to interpret the meaning of those
words.


> I have these discussions so I can improve my opinions that I
> have carefully crafted over 11 years, and that I frequently give to
others.

Some people frequently give others syphyllus, but I would not congratulate
them for the deed. I suggest you get more out of the gift than the others
do.


> If it is a matter of taste, then I don't mind anyhow.  I like hearing
others
> opinions, and as to why my ideas are wrong if they are (and some are.)

It is not a matter of taste but a matter of education. By very objective
criteria, hiding the logical identifier from users is just plain stupid. A
user must have access to the logical identifier to properly and to correctly
express queries.