Re: Primary Key Dilemma

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

From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 07/18/04 

Date: Sun, 18 Jul 2004 10:13:58 +0100

The reason you are having this perceived dilemma is that you haven't grasped
the difference between a Natural Key and a Surrogate Key. A Natural Key is
the subset of the attributes of an entity that uniquely identifies a row in
its table. This is often more than one column. For example if your users are
placing orders online then the key for the Orders table might be the
composite key (business_id, order_date). An IDENTITY column is never part of
the Natural Key of a table.

A Surrogate Key is an internally generated unique value (IDENTITY in SQL
Server), which for reasons of performance and to simplify administration is
sometimes used as the target of foreign key constraints in place of the
natural key. The surrogate key value is meaningless, it is purely for
internal use and should never need to be part of a composite key.

Both the Natural Key and the Surrogate Key should be declared as PRIMARY or
UNIQUE NOT NULL. In terms of *logical* design the Surrogate Key is optional
(in fact it's redundant) but the Natural Key is not. Some people will argue
that Surrogate Keys are unnecessary and just weaken your DB design while
others recommend using them for every table. In any case the IDENTITY column
shouldn't affect your decision about the keys for the tables in your system. 

-- 
David Portas
SQL Server MVP
--

Relevant Pages

  • Re: PK Null value
    ... An autonumber CityID column would ... be a suitable key of a Cities table for instance. ... Another advantage of a natural key is that it can make it unnecessary to ... Finally, if a surrogate key is used, and there can be one or more other ...
    (microsoft.public.access.gettingstarted)
  • Re: Newbie question about db normalization theory: redundant keys OK?
    ... a natural key and belongs in the database - regardless of whether it ... unlike a surrogate key it isn't generated inside the databse ... A natural key is neither more nor less than a familiar surrogate. ... That confusion is harmless enough until two or more people start to use the ...
    (comp.databases.theory)
  • Re: A real world example
    ... By this I am unclear what a natural key is a surrogate for. ... new surrogate key for our son - but many people treat SSN as a "natural ... is my DNA sequence a "familiar surrogate key"? ...
    (comp.databases.theory)
  • Re: How should I generate a primary key?
    ... The whole surrogate key model means that statement is sort of true, ... natural key if you require the meta data that the natural key will give you. ... client so I don't join to the client table. ... In any database large enough to start worrying about cascading updates ...
    (comp.databases)
  • Re: Surrogate Key
    ... The reason we are using Surrogate Key is so that we have a key which never changes ... We may not have SSN in DB bcoz of security reasons ...
    (microsoft.public.sqlserver.server)