Re: Primary Key Theory Question

From: Mike Sherrill <MSherrillnonono_at_compuserve.com>
Date: Sat, 03 Apr 2004 08:27:28 -0500
Message-ID: <l1fq60h3q0puk064v0pvjmafs3rdb7hnvi_at_4ax.com>


On Thu, 1 Apr 2004 15:47:40 +0200, "ben brugman" <ben_at_niethier.nl> wrote:

>> In my experience, a surrogate key takes the place of a candidate key,
>> and an artificial key isn't a key. An artificial key might uniquely
>> identify a row, but it doesn't uniquely identify what the row
>> represents.
>>
>I do not really understand what you are writing.
>
>If an artificial key uniquely identifies a row but not what the row
>represents, the row does neither uniquely identify what the
>row represents. This is what I read and do not understand.

I think you do understand it. If you start with this kind of table:

 Full_Name
 --
 Sherrill, Mike
 Sherrill, Mike

There's no key. You can't tell which row refers to which Mike Sherrill. (You can't really tell whether there are one or two of us, but that's a different point.) Add a sequence and you can identify the row,

 ID Full_Name
 --
 1 Sherrill, Mike
 2 Sherrill, Mike

but you still don't know which Mike Sherrill is which. I'd call that an artificial key. OTOH, we have security badges here. They're unique, but clumsy.

 Badge_Number Full_Name
 --
 ACH234A-B33F3E Sherrill, Mike
 ACH11DE-B53F7F Sherrill, Mike

To make life easier on yourself, you might add a sequence to this table.

 ID Badge_Number Full_Name
 --
 1 ACH234A-B33F3E Sherrill, Mike
 2 ACH11DE-B53F7F Sherrill, Mike

Since it takes the place of a key, I'd call it a surrogate.

-- 
Mike Sherrill
Information Management Systems
Received on Sat Apr 03 2004 - 15:27:28 CEST

Original text of this message