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

[David Portas]

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

Do you really allow the same Doctor, Department, etc to appear twice in its
table with different keys? If you don't declare unique natural keys then
that's the kind of problem you have. An IDENTITY isn't a *surrogate* key at
all unless the table also has a natural key - it's just a physical row
identifier.

--
David Portas
------------
Please reply only to the newsgroup
--

[Bruce Lewis]

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

"Trey Walpole" <treyNOpole@SPc omcastAM.net> writes:
[color=blue]
> I am a firm believer that natural keys should only be used to logically
> design/normalize the data. When it comes to the real reason for keys, data
> integrity, more often than not I have seen that natural keys are
> intrinsically not good physical primary keys.[/color]

This differs from my experience.
[color=blue]
> 1. Natural keys are, being natural and therefore user entered [i.e.,
> provided to the database by external means], fungible. If a user enters
> data, they must also be able to modify it. If data can be modified, then its
> value as a systemic primary key is gone. Yes, you can cascade updates to
> these, but why do it when it can be avoided to start with.[/color]

Why do it? To avoid duplicates of course. Why not do it? You don't
seem to be making any sort of case here.
[color=blue]
> 2. Natural keys are typically a composite of atomic attributes. If using a
> composite, these must be propagated to referencing tables as foreign keys.
> Your normalization drops below par, by having these [potentially] massively
> duplicated columns.[/color]

I agree that natural keys should be avoided because they're too large.
However, most business reports I see typically have columns that consist
of abbreviations chosen to make the report less wide. These make great
natural keys.
[color=blue]
> Attributes that are single, [supposedly] unique attributes (e.g., SSN),
> usually represent some official, governmentally recognized ID, and therefore
> have legal issues with being propagated throughout a system.[/color]

Yes, SSNs are problematic because too many organizations use them for
authentication, i.e. "You put Trey Walpole's SSN on this form, so you must
be Trey Walpole". Even in the absence of such stupid organizations,
privacy advocates oppose national IDs for a very good reason: such IDs
make it easy to create good databases that include people. However, I
assume the original poster had the opposite goal: make it easy to create
good databases.
[color=blue]
> Also, for amateurs and many professionals, natural keys are very often
> chosen incorrectly. e.g., I believe some combination of Name and other info
> has been used by my ISP as their primary key. My last name was entered into
> their system incorrectly, but they cannot fix it because their system will
> not allow it. Preposterous and poor design.[/color]

It sounds like this "prepostero us and poor design" could have been fixed
with a simple REFERENCES ... ON UPDATE CASCADE. Perhaps you should
offer them your services. I can't tell from your story whether or not
they used a poor choice of natural key.

Yes, people can make bad choices as to natural primary keys, but I think
this one additional opportunity to do bad database design is well worth
the risk, given the problems that arise from redundant or duplicate
data.
[color=blue]
> Surrogate keys generated by using the identity property are ideal for data
> integrity, because
> 1. They are static values [i.e., once entered, it does not change] and the
> DBA has control over allowing values in identity columns to be modified.[/color]

This is only meaningful if there's something wrong with ON UPDATE
CASCADE, which I think there isn't.
[color=blue]
> 2. They are singleton row ids.[/color]

And thus the problem. Earlier this year an e-mail alert system I wrote
was sending two copies when it should just send one. Looking into it,
the employee table had been doubled. I switch to a select distinct to
work around the problem, and someone deleted the duplicates. If we had
a natural primary key for the employee table, I doubt those duplicates
would have gone in.

Primary key constraint errors are your friends.

[Stijn Verrept]

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

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
news:NvCdnazr-9_dZlCiRVn-vA@giganews.com ...[color=blue]
> Do you really allow the same Doctor, Department, etc to appear twice in[/color]
its[color=blue]
> table with different keys? If you don't declare unique natural keys then
> that's the kind of problem you have. An IDENTITY isn't a *surrogate* key[/color]
at[color=blue]
> all unless the table also has a natural key - it's just a physical row
> identifier.[/color]

I never said I allow them to appear twice in the column, you have Unique
Constraint for that. I could use that as a natural key, but I prefer using
an int or smallint. I don't want to note Name, Firstname, ... in another
table as foreign key! Also in the application I don't see me writing:
select SN_Active from seniors where (SN_Name = :SNName) and (SN_FirstName =
:FirstName) and (SN_BirthDate = :SNBirthDate).


Stijn Verrept.

[Steve Kass]

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



Stijn Verrept wrote:
[color=blue]
>"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
>news:NvCdnaz r-9_dZlCiRVn-vA@giganews.com ...
>
>[color=green]
>>Do you really allow the same Doctor, Department, etc to appear twice in
>>
>>[/color]
>its
>
>[color=green]
>>table with different keys? If you don't declare unique natural keys then
>>that's the kind of problem you have. An IDENTITY isn't a *surrogate* key
>>
>>[/color]
>at
>
>[color=green]
>>all unless the table also has a natural key - it's just a physical row
>>identifier.
>>
>>[/color]
>
>I never said I allow them to appear twice in the column, you have Unique
>Constraint for that. I could use that as a natural key, but I prefer using
>an int or smallint. I don't want to note Name, Firstname, ... in another
>table as foreign key! Also in the application I don't see me writing:
>select SN_Active from seniors where (SN_Name = :SNName) and (SN_FirstName =
>:FirstName) and (SN_BirthDate = :SNBirthDate).
>
>
>Stijn Verrept.
>
>
>[/color]
How about putting a UNIQUE NOT NULL CLUSTERED constraint on the identity
column and putting the PRIMARY KEY NONCLUSTERED constraint on the
multi-column primary key?

That might confuse the anti-identity fanatics enough so they'll stop
complaining. You will have a natural primary key, so they won't think
the world is coming to an end, but you will go on as you always have,
using the identity column for its convenience in queries, FK
constraints, etc. ;)

SK

[Aaron Bertrand - MVP]

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

> How about putting a UNIQUE NOT NULL CLUSTERED constraint on the identity[color=blue]
> column and putting the PRIMARY KEY NONCLUSTERED constraint on the
> multi-column primary key?[/color]

Certainly, I suppose you could...

CREATE TABLE splunge
(
splungeID INT IDENTITY(1,1) NOT NULL UNIQUE,
email VARCHAR(128) PRIMARY KEY CLUSTERED
)
GO

CREATE TABLE blat
(
splungeID INT NOT NULL
FOREIGN KEY REFERENCES splunge(splunge ID)
)
GO

--
Aaron Bertrand
SQL Server MVP

http://www.aspfaq.com/

[Bob Badour]

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

"Steve Kass" <skass@drew.edu > wrote in message
news:3FCE13FE.2 070501@drew.edu ...[color=blue]
>
> Stijn Verrept wrote:
>[color=green]
> >"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
> >news:NvCdnaz r-9_dZlCiRVn-vA@giganews.com ...
> >[color=darkred]
> >>Do you really allow the same Doctor, Department, etc to appear twice in
> >>
> >>[/color]
> >its
> >
> >[color=darkred]
> >>table with different keys? If you don't declare unique natural keys then
> >>that's the kind of problem you have. An IDENTITY isn't a *surrogate* key
> >>
> >>[/color]
> >at
> >
> >[color=darkred]
> >>all unless the table also has a natural key - it's just a physical row
> >>identifier.
> >>
> >>[/color]
> >
> >I never said I allow them to appear twice in the column, you have Unique
> >Constraint for that. I could use that as a natural key, but I prefer[/color][/color]
using[color=blue][color=green]
> >an int or smallint. I don't want to note Name, Firstname, ... in another
> >table as foreign key! Also in the application I don't see me writing:
> >select SN_Active from seniors where (SN_Name = :SNName) and (SN_FirstName[/color][/color]
=[color=blue][color=green]
> >:FirstName) and (SN_BirthDate = :SNBirthDate).
> >
> >
> >Stijn Verrept.
> >
> >
> >[/color]
> How about putting a UNIQUE NOT NULL CLUSTERED constraint on the identity
> column and putting the PRIMARY KEY NONCLUSTERED constraint on the
> multi-column primary key?[/color]

Your question demonstrates profound confusion between logical and physical.
Uniqueness is a logical constraint. Clustering is purely physical and is an
attribute of an index not of a constraint. I realize that SQL confuses the
issue by inappropriately making uniqueness a property of a physical index
structure, but I see no reason to further confuse the issue.

[Steve Kass]

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



Bob Badour wrote:
[color=blue]
>"Steve Kass" <skass@drew.edu > wrote in message
>news:3FCE13FE. 2070501@drew.ed u...
>
>[color=green]
>>Stijn Verrept wrote:
>>
>>
>>[color=darkred]
>>>"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
>>>news:NvCdnaz r-9_dZlCiRVn-vA@giganews.com ...
>>>
>>>
>>>
>>>>Do you really allow the same Doctor, Department, etc to appear twice in
>>>>
>>>>
>>>>
>>>>
>>>its
>>>
>>>
>>>
>>>
>>>>table with different keys? If you don't declare unique natural keys then
>>>>that's the kind of problem you have. An IDENTITY isn't a *surrogate* key
>>>>
>>>>
>>>>
>>>>
>>>at
>>>
>>>
>>>
>>>
>>>>all unless the table also has a natural key - it's just a physical row
>>>>identifie r.
>>>>
>>>>
>>>>
>>>>
>>>I never said I allow them to appear twice in the column, you have Unique
>>>Constraint for that. I could use that as a natural key, but I prefer
>>>
>>>[/color][/color]
>using
>
>[color=green][color=darkred]
>>>an int or smallint. I don't want to note Name, Firstname, ... in another
>>>table as foreign key! Also in the application I don't see me writing:
>>>select SN_Active from seniors where (SN_Name = :SNName) and (SN_FirstName
>>>
>>>[/color][/color]
>=
>
>[color=green][color=darkred]
>>>:FirstName ) and (SN_BirthDate = :SNBirthDate).
>>>
>>>
>>>Stijn Verrept.
>>>
>>>
>>>
>>>
>>>[/color]
>>How about putting a UNIQUE NOT NULL CLUSTERED constraint on the identity
>>column and putting the PRIMARY KEY NONCLUSTERED constraint on the
>>multi-column primary key?
>>
>>[/color]
>
>Your question demonstrates profound confusion between logical and physical.
>Uniqueness is a logical constraint. Clustering is purely physical and is an
>attribute of an index not of a constraint. I realize that SQL confuses the
>issue by inappropriately making uniqueness a property of a physical index
>structure, but I see no reason to further confuse the issue.
>
>[/color]
Then don't.

Or else be honest. If you see no reason to confuse the issue further,
why quote me out of context by deleting my next paragraph, which made it
clear I was joking? [In case it's not clear, this is a rhetorical
question. I don't really want an answer from you.]

SK



[color=blue]
>
>
>[/color]

[Louis Davidson]

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


"Bob Badour" <bbadour@golden .net> wrote in message
news:Z-WdnVKmtPjLcFCi4 p2dnA@golden.ne t...[color=blue]
> "Louis Davidson" <dr_dontspamme_ sql@hotmail.com > wrote in message
> news:ex84CxQuDH A.1788@tk2msftn gp13.phx.gbl...[color=green]
> > The problem is with how it is used. If you start giving users access to
> > identity based values, you get into a bad spot where they want to make
> > changes to the value (in my line of work, we don't like the numbers 666[/color][/color]
in[color=blue][color=green]
> > account numbers) so using identities for user values is a bad idea. I[/color][/color]
use[color=blue][color=green]
> > them only for internal pointers that are never presented to users, since
> > they are not modifiable. I could use guids, or characters, or whatever[/color]
> for[color=green]
> > keys and no one would be the wiser.[/color]
>
> Keys are logical identifiers. They identify data for the user as well as[/color]
for[color=blue]
> the dbms. Preventing the user from seeing the identifier is just stupid.
>[/color]

Why? Do you want the user typing, remembering, or dealing with the
difference between ID=320983902 and 320984902 or 320983903? I certainly
don't. Invariably they would want something that they understood. Or what
about a GUID: 6969B66E-6A7A-4E89-B2D9-B35799B335C1 vs
DCF5DBC5-73B5-4009-9BBC-9312CFD6AD9D. Yick.

Kind of like you have a user name, an email address, fingerprints, an SSN
and DNA, etc that all identify you, but you only use a few of them here.
DNA is ugly, but as perfect of an identifier, but I don't know what my DNA
is, but it still exists.
[color=blue]
>[color=green][color=darkred]
> > > Natural keys are nothing more than familiar surrogates.[/color]
> >
> > You are kind of right here, but it is generally true that natural keys[/color][/color]
can[color=blue][color=green]
> > change, because in the world, things can change. Identities cannot[/color]
> change.
>
> It is not a "kind of" rightness. The statement is obviously and
> self-evidently right to anyone with at least a minimal education in the
> fundamentals of data management.
>[/color]
I suppose. But I don't think that the statement means anything. I don't
agree that all natural keys are familiar surrogates. Not every natural key
was initally randomly chosen. Some level of thought was placed to choosing
a name, hence it is not just a familar surrogate. A surrogate key (the word
surrogate meaning to take the place of, and the definition of a surrogate
key is: A unique primary key generated by the RDBMS that is not derived from
any data in the database and whose only significance is to act as the
primary key. I would remove the word primary from the sentence and we have
what I would agree with the definition)

They are very much alike, but I don't think you can state that a natural key
is a surrogate, ever. They are both keys, in that they both are
determinants, which is why we have called them keys.


--
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql@hotmail. com)
Compass Technology Management

Pro SQL Server 2000 Database Design

http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

[Louis Davidson]

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

"Stijn Verrept" <sverrept@nospa n.vub.ac.be> wrote in message
news:OFELqOYuDH A.3496@TK2MSFTN GP11.phx.gbl...[color=blue][color=green]
> > In cases like e-mail address and SSN (and in fact most cases), I still
> > prefer your route, where there is a surrogate key (IDENTITY) that[/color][/color]
prevents[color=blue][color=green]
> > me from having to cascade changes all over the place, and store larger
> > foreign keys.[/color]
>
> Ok I agree that sometimes natural keys are good. But mind the word:[/color]

NATURAL KEYS ARE ALWAYS GOOD. I was standing on my desk shouting if you
didn't see it :)

Just not always preferrable as PRIMARY KEYS! Always put keys on EVERY
unique combination (that doesn't include other unique combiniations ( if ID
is a unique key, then ID, Name should not be, use a simple index in this
case) for the sake of your data. Look up Boyce Codd normal form for more
information.

--
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql@hotmail. com)
Compass Technology Management

Pro SQL Server 2000 Database Design

http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

[color=blue]
> sometimes :). After reading through this thread I got the impression that
> natural keys are used more than surrogate keys, while in practice I use
> identity with surrogate keys almost all the time.
>[color=green]
> > Now, you might think, "why not bring SSN into the FirstName + MiddleName[/color][/color]
+[color=blue][color=green]
> > LastName key? That would make it unique." Yes, and hideously large.[/color][/color]
If[color=blue][color=green]
> > SSN is unique, then why not just use SSN as the key? Again, it's large[/color]
> even[color=green]
> > on its own (CHAR(9) and surely to become CHAR(10) in our lifetimes), so[/color][/color]
I[color=blue][color=green]
> > fail to see the benefit of repeating the value in every related table,[/color][/color]
DRI[color=blue][color=green]
> > or not.[/color]
>
> Indeed, and I don't know about American legislation but maybe in the[/color]
future[color=blue]
> (or even now) you can have people work for you who don't have a SSN (who
> work from a distance country for example) and then you'll get stuck again.
>[color=green]
> > Sorry about the earful, sometimes I get a little typographical diarrhea.
> > Hopefully that was at least marginally intelligible.[/color]
>
> It was :)
>
>
> Stijn Verrept.
>
>[/color]

[Louis Davidson]

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

BINGO! This is the point that seems to be missed by most every one. Thank
you David

--
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql@hotmail. com)
Compass Technology Management

Pro SQL Server 2000 Database Design

http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
news:NvCdnazr-9_dZlCiRVn-vA@giganews.com ...[color=blue]
> Do you really allow the same Doctor, Department, etc to appear twice in[/color]
its[color=blue]
> table with different keys? If you don't declare unique natural keys then
> that's the kind of problem you have. An IDENTITY isn't a *surrogate* key[/color]
at[color=blue]
> all unless the table also has a natural key - it's just a physical row
> identifier.
>
> --
> David Portas
> ------------
> Please reply only to the newsgroup
> --
>
>[/color]

[Bob Badour]

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

"Louis Davidson" <dr_dontspamme_ sql@hotmail.com > wrote in message
news:#7x9NScuDH A.2360@TK2MSFTN GP09.phx.gbl...[color=blue]
>
> "Bob Badour" <bbadour@golden .net> wrote in message
> news:Z-WdnVKmtPjLcFCi4 p2dnA@golden.ne t...[color=green]
> > "Louis Davidson" <dr_dontspamme_ sql@hotmail.com > wrote in message
> > news:ex84CxQuDH A.1788@tk2msftn gp13.phx.gbl...[color=darkred]
> > > The problem is with how it is used. If you start giving users access[/color][/color][/color]
to[color=blue][color=green][color=darkred]
> > > identity based values, you get into a bad spot where they want to make
> > > changes to the value (in my line of work, we don't like the numbers[/color][/color][/color]
666[color=blue]
> in[color=green][color=darkred]
> > > account numbers) so using identities for user values is a bad idea. I[/color][/color]
> use[color=green][color=darkred]
> > > them only for internal pointers that are never presented to users,[/color][/color][/color]
since[color=blue][color=green][color=darkred]
> > > they are not modifiable. I could use guids, or characters, or[/color][/color][/color]
whatever[color=blue][color=green]
> > for[color=darkred]
> > > keys and no one would be the wiser.[/color]
> >
> > Keys are logical identifiers. They identify data for the user as well as[/color]
> for[color=green]
> > the dbms. Preventing the user from seeing the identifier is just stupid.
> >[/color]
>
> Why? Do you want the user typing, remembering, or dealing with the
> difference between ID=320983902 and 320984902 or 320983903?[/color]

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.

Preventing the user of the data from seeing the identifier for the data is
just plain stupid.

[Bob Badour]

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

"Louis Davidson" <dr_dontspamme_ sql@hotmail.com > wrote in message
news:#7x9NScuDH A.2360@TK2MSFTN GP09.phx.gbl...[color=blue]
>
> "Bob Badour" <bbadour@golden .net> wrote in message
> news:Z-WdnVKmtPjLcFCi4 p2dnA@golden.ne t...[color=green]
> > "Louis Davidson" <dr_dontspamme_ sql@hotmail.com > wrote in message
> > news:ex84CxQuDH A.1788@tk2msftn gp13.phx.gbl...[/color][/color]
[color=blue]
> Kind of like you have a user name, an email address, fingerprints, an SSN
> and DNA, etc that all identify you, but you only use a few of them here.
> DNA is ugly, but as perfect of an identifier, but I don't know what my DNA
> is, but it still exists.[/color]

You seem ignorant of a few basic facts regarding DNA. Multiple individuals
(identical twins as well as artificial clones) largely share
indistinguishab le DNA. Almost all individuals have multiple DNA patterns due
to viral infections and random mutations. Some individuals, chimeras, have
multiple DNA patterns that are very different. The use of stem cell
treatments will introduce additional DNA patterns into individuals.

Before I die, I hope that none of my cells have my original DNA. Of course,
I also hope that won't be for several millenia at least.

Oh, and DNA fails the simplicity criterion for effective logical references.

[Bob Badour]

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

"Louis Davidson" <dr_dontspamme_ sql@hotmail.com > wrote in message
news:#7x9NScuDH A.2360@TK2MSFTN GP09.phx.gbl...[color=blue]
> "Bob Badour" <bbadour@golden .net> wrote in message
> news:Z-WdnVKmtPjLcFCi4 p2dnA@golden.ne t...[color=green]
> > "Louis Davidson" <dr_dontspamme_ sql@hotmail.com > wrote in message
> > news:ex84CxQuDH A.1788@tk2msftn gp13.phx.gbl...[color=darkred]
> > > > Natural keys are nothing more than familiar surrogates.
> > >
> > > You are kind of right here, but it is generally true that natural keys[/color][/color]
> can[color=green][color=darkred]
> > > change, because in the world, things can change. Identities cannot[/color]
> > change.
> >
> > It is not a "kind of" rightness. The statement is obviously and
> > self-evidently right to anyone with at least a minimal education in the
> > fundamentals of data management.
> >[/color]
> I suppose. But I don't think that the statement means anything.[/color]

Then I can only conclude you lack the ability to comprehend relatively
simple written english.

[color=blue]
> I don't
> agree that all natural keys are familiar surrogates. Not every natural[/color]
key[color=blue]
> was initally randomly chosen.[/color]

No key is randomly chosen and nothing in surrogacy implies or suggests
randomness. A truly random choice would suggest a psychotic break with
reality. Nothing that anyone can use to identify me is me, and in that sense
any key that identifies me is only a surrogate for me.

Only values are self-identifying, but we use representations as surrogates
even for values.

[Bob Badour]

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

"Louis Davidson" <dr_dontspamme_ sql@hotmail.com > wrote in message
news:#7x9NScuDH A.2360@TK2MSFTN GP09.phx.gbl...[color=blue]
>
> "Bob Badour" <bbadour@golden .net> wrote in message
> news:Z-WdnVKmtPjLcFCi4 p2dnA@golden.ne t...[color=green]
> > "Louis Davidson" <dr_dontspamme_ sql@hotmail.com > wrote in message
> > news:ex84CxQuDH A.1788@tk2msftn gp13.phx.gbl...[/color]
> Some level of thought was placed to choosing
> a name, hence it is not just a familar surrogate.[/color]

Again, I suggest the above sentence suggests you lack the ability to
comprehend relatively simple written english. Perhaps, if you opened a
dictionary and looked up the word 'surrogate', you might improve your
competence at extracting meaning from english.

A surrogate implies no particular level of thought.

[color=blue]
> A surrogate key (the word
> surrogate meaning to take the place of, and the definition of a surrogate
> key is: A unique primary key generated by the RDBMS that is not derived[/color]
from[color=blue]
> any data in the database and whose only significance is to act as the
> primary key. I would remove the word primary from the sentence and we[/color]
have[color=blue]
> what I would agree with the definition)[/color]

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.

[Bob Badour]

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

"Louis Davidson" <dr_dontspamme_ sql@hotmail.com > wrote in message
news:#7x9NScuDH A.2360@TK2MSFTN GP09.phx.gbl...[color=blue]
> "Bob Badour" <bbadour@golden .net> wrote in message
> news:Z-WdnVKmtPjLcFCi4 p2dnA@golden.ne t...[color=green]
> > "Louis Davidson" <dr_dontspamme_ sql@hotmail.com > wrote in message
> > news:ex84CxQuDH A.1788@tk2msftn gp13.phx.gbl...[/color]
> They are very much alike, but I don't think you can state that a natural[/color]
key[color=blue]
> is a surrogate, ever. They are both keys, in that they both are
> determinants, which is why we have called them keys.[/color]

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

Beyond that, I have been well conditioned to respond to my name, which makes
it useful for those who wish to get my attention. Although, given the
similarity in pronunciation to "Mom", this conditioning can be somewhat
inconvenient at the mall and in restaurants.