What is the need of surrogate key;why primary key not used as surrogate key

Showing Answers 1 - 6 of 6 Answers

Sahitya Bindu

  • Sep 12th, 2006

Surrogate Key is an artificial identifier for an entity.In surrogate key values are generated by the system sequentially(Like Identity property in SQL Server and Sequence in Oracle). They do not describe anything.

Primary Key is a natural identifier for an entity. In Primary keys all the values are entered manually by the user which are uniquely identified. There will be no repeatition of data.

Need for surrogate key not Primary Key

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.

  Was this answer useful?  Yes


  • Sep 21st, 2006

Surrogate key is system generated unique number.

Identity in SQL, Sequence in Oracle, Sequence Generator in Informatica. 

For Example : 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.


  • Nov 11th, 2006

One more reason for using surrogate keys in a Datawarehouse.

1.  As data is extracted from disparate  sources , where  in each source might have primary keys with  data types or formats inherent to the underlying database, if the same primary keys are are used in the DW , there would be   inconsistencies in representation of data which would make quering  of the database a difficult job ,  so  the surrogate keys are implemented to circumvent these kind of situations .

Datawarehousing depends on the surrogate key not primary key, for suppose if u r taking the product price it will change over the time, but product no. will not  change but price will change over the time to maintain the full hystorical data  about the product it is necessary in datawarehouse.

  Was this answer useful?  Yes


  • Jul 16th, 2010

In DW, a fact table stores a few Primary keys of the corresponding Dimension tables. It will be always easier to get the desired record from the fact table when query is fired based upon a particular unique column and that is the another reason to introduce surrogate key.

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.


Related Answered Questions


Related Open Questions