Re: Surrogate primary key plus unique constraint vs. natural primary key: data integrity?

Le mardi 12 mars 2013 17:26:12 UTC+1, Roy Hann a écrit :
> Cimode wrote:
>
>
>
> > All unique identifiers are at some point in time a surrogate key.
>
>
>
> No identification exists in nature.
An no need to distinguish things either. Any theory is nothing but natural, including relational theory.

> > Only designer's subjectivity qualify them as natural or primary key.
>
>
>
> Maybe I mistake your point but you seem to suggest it is decided on a
>
> whim.
Your assumption. Not mine.

> It is a consequence of defining the enterprise of interest. A
>
> value assigned outside the enterprise of interest that is a key
>
> within it is "natural".

An *enterprise of interest* does not say anything about the fact that a surrogate key may at some future point in time be considered a natural key. But the point is that subjectivity can not be taken from the equation in any scheme involving establishing a unique identifier.

> A credit card number is a synthetic/surrogate key in the card issuer's
>
> database but it's a natural key in the merchant's database.
See above.

> I will quckly concede that "natural" key is a poor choice of
>
> terminology. "Alien key" might be better, or "extant key". I'm sure we
>
> can think of yet better names.
>
>
>
> > In the context of guaranteeing data integrity, I am still amazed at
>
> > how such implementation-driven minor point triggers so much debate.
>
>
>
> Well when you work with bone-heads who actually ban natural keys from
>
> being propagated as foreign keys, and who introduce a new surrogate in
>
> every table, and in effect create link-lists of tables that you have
>
> to navigate to do anything, you don't give in without a fight.
Sorry, but this is your battle.

I stopped making myself sick on this issue for a while since I know who my audience is and what it knows about database theory.

Explaining distinguishibility seems a more important challenge to me than focusing the debate on whether they feel more comfortable with a surrogate or a natural. I have too many developers who do not understand the concept on a logical level. IMHO.

Regards
> Roy
Received on Tue Mar 12 2013 - 19:24:19 CET