Re: Primary Key Theory Question

"Mike Sherrill" <MSherrillnonono_at_compuserve.com> wrote in message news:cptn601t2escr4r6j7b0muuik41vrc4a7h_at_4ax.com...
> On Thu, 1 Apr 2004 10:27:18 +0200, "ben brugman" <ben_at_niethier.nl>
> wrote:
>
> >> Do you know what "surrogate" means? (I think you're talking about an
> >> artificial key, not a surrogate key. I could be wrong.)
> >>
> >I think I 'know' what "surrogate" means.
> >
> >Artificial : SSN, ISBN.
> >
> >Surrogate : a unique key (often extra), mostly used only in internal
> >programming.
>
> Where did you find those definitions?
>
> 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.

My (big questionmark) reading :
1. artificial key uniquely identifies a row. 2. artificial key does not uniquely identify were the row stands for.

From 1 and 2 I come to the ?? valid ?? conclusion that 3. The row DOES NOT uniquely identify were the ROW stands for.

The other way round.
And this 'conflicts' with your description ??? A. A row uniquely identifies were the row stands for. (Assumed to be true ?) B. artificial key uniquely identifies a row. (True)

If A and B are true then :
C. artificial key uniquely identifies were the row stands for.

For the concept of surrogate key, there are many places were they are described and defined. In general (I think) they are used in 'almost' the same way.
(Artificial keys are things like ISBN, SSN and other artificial generated keys but with a meaning in the actual world. Often there is no differentiation between artificial and surrogate and therefore the artificial is uses were others use the word surrogate. Some people insist in a certain technique to generate a surrogate, I do not.)

> Where did you find those definitions?
>
I used the words but try to use the words as used most in the database threads, but realise that there are often many different and conflicting definition of the words. Below a part of a thread, describing surrogate keys. (I am aware of more and of other definitions).

greetings
ben brugman

The following is from another thread :

C.J. Date offers a great explanation of what the rest of the world understands as a surrogate key.

From _An Introduction to Database Systems_, 7th edition, Addison Wesley Longman, 1999, p. 444:

<excerpt>

"Surrogate keys are keys in the usual relational sense but have the following specific properties:

"* They always involve exactly one attribute.

"* Their values serve solely as surrogates (hence the name) for the entities they stand for. In other words, such values serve merely to represent the fact that the corresponding entities exists-they carry no additional information or meaning whatsoever.

"* When a new entity is inserted into the database, it is given a surrogate key value that has never been used before and will never be used again, even if the entity in question is subsequently deleted.

"Ideally, surrogate key values would be system-generated, but whether they are system- or user-generated has nothing to do with the basic idea of surrogate keys as such.

"It is worth emphasizing that surrogates are not (as some writers seem to think) the same thing as "tuple IDs." For one thing-to state the obvious-tuple IDs identify tuples and surrogates identify entities, and there is certainly nothing like a one-to-one correspondence between tuples and entities (think of tuple IDs for derived tuples in particular). Furthermore, tuple IDs have performance connotations, while surrogates do not; access to a tuple via its tuple ID is usually assumed to be fast (we are assuming here that tuples-at least, tuples in base relations-map fairly directly to physical storage, as is in fact the case in most of today's products). Also, tuple IDs are usually concealed from the user, while surrogates must not be (because of The Information Principle); in other words, it is not possible to store a tuple ID as an attribute value, while it certainly is possible to store a surrogate as an attribute value.

"In a nutshell: Surrogates are a logical concept; tuple IDs are a physical concept."

</excerpt>

> --
> Mike Sherrill
> Information Management Systems
Received on Thu Apr 01 2004 - 15:47:40 CEST