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

From: Roy Hann <specially_at_processed.almost.meat>
Date: Tue, 12 Mar 2013 16:26:12 +0000 (UTC)
Message-ID: <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
Received on Tue Mar 12 2013 - 17:26:12 CET

Original text of this message