Data Warehousing Community Forum
February 10, 2012, 12:53:09 am
Home
Help
Search
Login
Register
Data Warehousing Community Forum
>
Data Warehousing
>
Data Warehousing Concepts
>
What is a Surrogate key?
Pages: [
1
]
Go Down
« previous
next »
Print
Author
Topic: What is a Surrogate key? (Read 1370 times)
Sipra
Founder
Reputation: +59/-0
Offline
Posts: 323
Am the King...
What is a Surrogate key?
«
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
Sipra
Founder
www.dwforum.net
Whoever
Administrator
Reputation: +34/-0
Offline
Posts: 124
Surrogate key - updates
«
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
Posts: 23
World is Mine
Re: What is a Surrogate key?
«
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
Posts: 25
Re: What is a Surrogate key?
«
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
Posts: 17
Re: What is a Surrogate key?
«
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.
Logged
Pages: [
1
]
Go Up
Print
« previous
next »
Jump to:
Please select a destination:
-----------------------------
General Category
-----------------------------
=> Introductions
=> Anouncements & News
=> Suggestions
=> General Discussion
=> Job Opportunities
-----------------------------
Data Warehousing
-----------------------------
=> Data Warehousing Concepts
=> Data Modeling
-----------------------------
Databases
-----------------------------
=> Oracle
===> PL/SQL
=> Microsoft SQL Server
=> IBM DB2
=> Teradata
-----------------------------
ETL Tools
-----------------------------
=> Informatica
===> Installation & Configuration
=> Data Stage
=> Ab Initio
=> Oracle Warehouse Builder
=> DTS/SSIS
-----------------------------
Reporting Tools
-----------------------------
=> Business Objects
=> Cognos
=> Crystal Reports
=> Hyperion
=> Brio
=> Microstrategy
-----------------------------
Operating Systems
-----------------------------
=> UNIX / Linux
===> Unix Shell Scripting
=> Windows
-----------------------------
Other Technologies & Tools
-----------------------------
=> JAVA