Data Warehousing Community Forum
February 10, 2012, 12:53:09 am

Pages: [1]   Go Down
  Print  
Author Topic: What is a Surrogate key?  (Read 1370 times)
Sipra
Founder
*****

Reputation: +59/-0
Offline Offline

Posts: 323


Am the King...


View Profile WWW
« on: June 11, 2007, 10:10:39 am »

A Surrogate key is a substitution for the natural primary key.

It is  a unique identifier or number ( normally created by a database sequence generator ) for each record of a dimension table that can be used for the primary key to the table.
Logged

Whoever
Administrator
*****

Reputation: +34/-0
Offline Offline

Posts: 124



View Profile WWW
« Reply #1 on: July 18, 2007, 03:04:09 pm »

A Surrogate key is a substitution for the natural primary key.
It is a unique identifier or number (normally created by a database sequence generator) for each record of a dimension table that can be used for the primary key to the table.

A Surrogate key is useful because natural keys may change.
Logged

If most people said what’s on their minds, they’d be speechless.
mrinal
DW Fresher
*

Reputation: +8/-0
Offline Offline

Posts: 23


World is Mine


View Profile
« Reply #2 on: December 18, 2008, 08:47:45 am »

Just adding on whatever posted earlier,for additional info,

A surrogate key is a simple Primary key which maps one to one with a Natural compound Primary key.  The reason for using them is to alleviate the need for the query writer to know the full compound key and also to speed query processing by removing the need for the RDBMS to process the full compound key when considering a join.

For example, an shipment could have a natural key of ORDER + ITEM + SHIPMENT_SEQ.  By giving it a unique SHIPMENT_ID, subordinate tables can access it with a single attribute, rather than 3.  However, it's important to create a unique index on the natural key as well.

Need : You are extracting Customer Information from OLTP Source and after ETL process, loading customer information in a dimension table (DW).  If you take SCD Type 1, Yes you can use Primary Key of Source CustomerID as Primary Key in Dimension Table. But if you would like to preserve history of customer in Dimension table i.e. Type 2. Then you need another unique no apart from CustomerID.  There you have to use Surrogate Key. 

Another reason : If you have AlphaNumeric as a CustomerID. Then you have to use surrogate key in Dimension Table. It is advisable to have system generated small integer number as a surrogate key in the dimension table. so that indexing and retrieval is much faster.

If a column is made a primary key and later there needs a change in the datatype or the length for that column then all the foreign keys that are dependent on that primary key should be changed making the database Unstable
Surrogate Keys make the database more stable because it insulates the Primary and foreign key relationships from changes in the data types and length.
« Last Edit: December 18, 2008, 10:59:11 am by mrinal » Logged
herbert11
DW Apprentice
**

Reputation: +4/-0
Offline Offline

Posts: 25


View Profile
« Reply #3 on: August 16, 2011, 12:58:48 pm »

Surrogate key is a unique identification key used in data warehouse. It can be used as an alternative to production key. There is a difference between these two, production key can have alpha numeric values, but surrogate key can have only numeric values. It is used to identify an entity or object in database.
Logged
alexa007
DW Fresher
*

Reputation: +1/-0
Offline Offline

Posts: 17


View Profile
« Reply #4 on: September 13, 2011, 03:11:45 pm »

A surrogate key is an artificial value that has no meaning to the user, but is guaranteed to be unique by the database itself. A surrogate key in a database is a unique identifier for either an entity in an object in the database. The surrogate key is not derived from application data. Smiley Smiley Smiley
Logged
Pages: [1]   Go Up
  Print  
 
Jump to: