Re: identity columns

On 6 Jan 2002 08:30:21 -0800, 71062.1056_at_compuserve.com (--CELKO--) wrote:

>>> There seems to be a lot of talk in this NG about the good, bad and
>ugly
>aspects of primary keys - especially Identity columns. <<
>
There may be good reasons why a particular proprietary system of creating a numerical psuedo column is full of fleas; but having a numerical, meaningless, system assigned unique primary key can be a good thing, in my experience.

The arguments that win my heart for numerical "artificial" PKs are that they are under our control, and that numerical indeces are likely to perform better than indeces based on strings. Think of the PK as a numerical 'handle" to your data row. The quickest way to fetch that row (absent some proprietary thing such as Oracles's "rowid" ) would be by its indexed numerical PK.

They make great Foreign keys, and chances are you will want indexes on all the columns that are FK's to facilitate Joins.

One big problem with "natural" keys is that very often there's no such unique animal. You'll need a fragmented PK which can be awkward , and in any case, the values you user tells you never change, will change. Your user is a fine fellow but he didnt think the odd case of the transexual swapping M and F was worth mentioning when there were so many more important issues he wanted you to get right. . It happens every time and tell me it aint so?

There's a theoretical argument against them as follows:

It aint 3NF:
FirstName,Lastname in combination are unique in tje PERSON table. Your numerical PK AND your unique First NAme Last NAme mean in effect that you have two candidate keys, and shame on you!. Rebuttal:
What happens when your PERSON "sees the light" and changes his name to Malcolm X? The unique primary key goes bye-bye but your PK stays, and all the records which have the PERSON_ID as FK are rock solid, whereas all the ones with "Malcolm Small" are toast. Your "natural" PK was an illusian, it was "accidentally" a PK because the current values happened to be jointly unique, but they were subject to change after all. Lest you think the Malcolm X example is far fetched...what about Miss Piggy and Mrs Kermit T Frog? What about Mr Jones who becomes Dr Jones, Rev Jones, or Capt Jones KCB? John Q Kaddidlhopper Jr who decides he's a big boy now and uses John Q Kaddidlhopper without the Jr?

The only down side is that instead of COLOR=red you see COLOR_ID=12345 (the PK of the RED column in the COLOR table). Easily fixed with a view joining the base table to the color table on the numerical COLOR_ID. If you can see a serious down side, I'm all ears, but I havent run into one since I started playing with (and getting paid to muck with) this stuff in around '84 or so. Maybe I was just lucky?

YMMV but I'll take the numeric "meaningless" primary key, thank you very much.   Received on Fri Jan 18 2002 - 04:02:27 CET