Re: Surrogate Key

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Anith Sen (anith_at_bizdatasolutions.com)
Date: 01/06/05 

Date: Thu, 6 Jan 2005 09:32:34 -0600

Surrogate key is an unfamiliar attribute, assigned to an entity for the sole
purpose of identification. The assignment can be done either by the user or
by the system. The primary benefits of a surrogate key are simplicity and
stability. Being applicable at the logical level, surrogate keys have
nothing by themselves associated with performance, contrary to popular
perception.

The concept of surrogates is first introduced in 74 by Hall & Owlett, later
incorporated to relational model as a "semantic" extension by Codd. However,
later studies on the subject have suggested that there is nothing special to
surrogates other than being an stable and simple, though generally
unfamiliar. In that sense, one might consider a natural key as nothing but a
familiar surrogate.

Many database products offer key generation facilities, some conforming to
relational requirements and some not. Either way, once generated and
assigned as an entity identifier in a row, its logical behavior is no
different from any other identifiers. For this reason, not much importance
has been given to notion of surrogates in formal database studies. Also,
standard bodies like ISO, have never deemed surrogates to be worthy of a
formal definition, due to its negligible distinction from other identifiers.

Some articles & discussions on this topic, esp. on product specific forums
generally emphasize on the benefits or drawbacks of using a surrogate key.
It is often done with a skewed example or two that suit the author's whims
where he/she may guide the reader into believing somehow surrogates are
"good" or "bad". In many cases the author might miss or deliberately ignore
certain basic concepts to put forth his/her point of view.

A good way of weeding out the chaff from good stuff is to learn the
foundations behind a key and identify the characteristics of a good key.
Thus you can decide on your own whether using an additional identifier is
worthy an effort in your specific design scenario or not. 

-- 
Anith

Relevant Pages

  • Re: Little design mistakes that can be easily avoided (2): Listenning to CELKO (and CELKO alikes)
    ... And how, specifically, does any of these paragraphs contradict Celkos ... This means that a surrogate ought to act like an index; ... *headings* in equi-joins. ... A surrogate key value does indeed have a meaning. ...
    (comp.databases.theory)
  • Re: How to ID a record
    ... > or trying to alter a primary key later and all the other probabilities. ... In reality a surrogate key doesn't really identify a record except so far as ... "Ray" wrote in message ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Little design mistakes that can be easily avoided (2): Listenning to CELKO (and CELKO alikes)
    ... And how, specifically, does any of these paragraphs contradict Celkos ... It directly contradicts the following paragraph in Celko's ... This means that a surrogate ought to act like an index; ... The only issue I see is about the visibility of surrogate key ...
    (comp.databases.theory)
  • Re: Negative Numbers in "Identity" or" Autonumber" fields
    ... I have run in to this construct a few times in the past, in papers ... In the RM world it can function as a surrogate. ... run into this is security papers. ... One argument for its use as a surrogate key is that ...
    (comp.databases.theory)
  • Re: A real world example
    ... I disagree that the concept of surrogate vs. natural is useful. ... By this I am unclear what a natural key is a surrogate for. ... It is an arbitrary identifier chosen by my parents. ... The arguments against natural keys relate mostly to control. ...
    (comp.databases.theory)