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

"Roy Hann" <specially_at_processed.almost.meat> wrote in message <news:khnkv4$o4a$1_at_speranza.aioe.org>...

> Cimode wrote:
>
>> All unique identifiers are at some point in time a surrogate key.
>
> That is a truism. No identification exists in nature.
>
>> 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. 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".
>
> 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.
>
> 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.
>
> --
> Roy

When you do that navigation, do the bone-heads then b*tch about your bringing the DBMS to its knees just to link a row in Z to its distantly corresponding row in A?

select A.ID, A.Foo, A.Bar
from A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z where A.ID = B.A_ID and B.ID = C.B_ID and C.ID = D.C_ID and ... and Y.ID = Z.Y_ID and Z.ID = ? Received on Thu Mar 14 2013 - 23:00:41 CET