What is Surrogate Key ?

Explain with Example

0 members like this

▶ Reply to This

Replies to This Discussion

Permalink Reply by Neil Aakash on November 5, 2012 at 3:02pm

There are two definitions of a surrogate:

Surrogate (1) – Hall, Owlett and Codd (1976)

A surrogate represents an entity in the outside world. The surrogate is internally generated by the system but is nevertheless visible to the user or application.

Surrogate (2) – Wieringa and De Jonge (1991)

A surrogate represents an object in the database itself. The surrogate is internally generated by the system and is invisible to the user or application

it is necessary to distinguish between the surrogate key and the primary key. Typically, every row would have both a primary key and a surrogate key. The primary key identifies the unique row in the database, the surrogate key identifies the unique entity in the modelled world; these two keys are not the same. For example, table Staff may contain two rows for "John Smith", one row when he was employed between 1990 and 1999, another row when he was employed between 2001 and 2006. The surrogate key is identical (non-unique) in both rows however the primary key will be unique.

▶ Reply

Permalink Reply by bathwalshaili on November 7, 2012 at 1:19pm

Hi Kannan,

Surrogate key is used to identify each row uniquely. It is also known as artificial or identity key. The surrogate key is system generated artificial primary key and has a defined incremental value for each row in a table.

Surrogate key is used as the primary key in the dimension table.

Please feel free to contact if you require more details.

Regards,

Shaili

sbathwal@infocepts.com

▶ Reply

Permalink Reply by Rohit Agrawal on November 8, 2012 at 11:54am

Hi Kannan,

Surrogate key is a sequence generator(mostly numeric) which is embedded into the dimension table to identify a unique row.

It is most important when you are implementing slowly changing dimension concept (generally type 2 and type 3) when populating the dimension table because it would only be unique when a record with same natural key would be inserted.

Please let me know any question.

Thanks,

Rohit

ragrawal@infocepts.com