At 11:55 PM -0400 7/11/2000, Dave Watts wrote:
> > > This is also true, but may actually cause you to require an
>> > extra step, as you might have the requirement to prevent duplicate
>> > values in your natural primary key, even if it's not being
>> > officially used as a primary key. For example, you wouldn't
>> > want to have duplicate entries with the same first name, last
>> > name and SSN, even if you're using an identity column as a
>> > surrogate key. Again, I'm willing to live with that, and
>> > heartily recommend using identity columns, or whatever surrogate
>> > key mechanism the database offers.
>>
>> Yes, this is true...
>>
>> ...But, here again use of Identity solves a problem:
>>
>> What if you used FN LN and Street address as primary key?
>>
>> Then, You have two different AlGores living at the same address!
>>
>> You would somehow have uniquify these two different records'
>> primary keys..
>>
>> Enter Identity, problem solved...
>
>I think you've got me confused with someone else; I'm saying you SHOULD use
>primary keys!
>
No, I completely agree with your well-stated positions. I merely
wanted to suggest that an identity field would be a good uniquifier
for duplicate "natural" keys where they, in fact, represent different
records.
You could examine each record and and include other fields, but
different fields might be required for each pair of duplicates:
FN LN Address SSN Age Origin
---- --------- ----------------- --------------- ----- ------------
Sam Johnson 123 Main None None 75 France
Sam Johnson 123 Main None None 72 France
Bill Jones 112 Elm Street Unknown 60 UK
Bill Jones 112 Elm Street Unknown 60 Detroit
Both of these are valid duplicates (I can easily determine that by
examination), but they have different "natural" uniquifiers...
In some tables, even if you included every "natural" field as part of
the primary key, you are not guaranteed uniqueness.
So, as a tie-breaker, you could add an identity field... then, as it
turns out, it alone would satisfy the requirements of a primary key
(so you don't need the other fields).
BTW, these represent "real" situations such as a database of criminal
incidents or historical records, where the information is incomplete
and the subject is not available to supply it.
Incidentally, there is a case to be made *against* using natural keys
as primary keys.
Celko talks about the problems when "boss" & "employee" change their names.
The classic case is Chrysler Corp. They once used a few positions in
their part numbers to signify:
material being used
manufacturing process being used
So an engine block might carry a part number of:
xxxx-IR-yyyy-CA-zzz
to represent the fact that the block was made of IRon and was CAst.
Now, when they decided to substitute a MAchined ALuminum part, the
change rippled through the entire database.
Imagine the poor dba with a parts-explosion database.
An arbitrary key would represent the part and its relationship to
other parts and *not* need to be changed... a much better choice in
the real world, IMO *.
* Chrysler eventually went to arbitrary part numbers and eliminated a
whole set of problems
Identity is merely a convenient and efficient tool, supplied by some
dbs, to provide an unique, arbitrary key...
If it is available I use it, if not, I simulate it with MaxID (or some such).
... Good discussion, tho!
Dick
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
