what is the difference between primarykey,naturalkey ,candidatekey and Surrogate key with some example

Question

Hi all

I just migrated to datawarehousing domain from .net

I need some help in understaind the difference between keys used in datawarehousing...

Somebody please explain me what is the difference between primarykey,naturalkey ,candidatekey and Surrogate key
 with some example.

---

ilikemicrosoft

Answer 1

surendiran,

  Please find the below links which gives you enough information.

http://social.msdn.microsoft.com/Forums/en-us/sqlgetstarted/thread/27bd9c77-ec31-44f1-ab7f-bd2cb13129be

http://www.agiledata.org/essays/keys.html

http://weblogs.sqlteam.com/mladenp/archive/2009/10/06/Why-I-prefer-surrogate-keys-instead-of-natural-keys-in.aspx

Tinto

Answer 2

primarykey: unique not null column(s) which can be used to identify each row in the table individually

naturalkey: A column with values such that they can be used directly as the attribute key to lookup dimension to fact relationship. e,g countrycode uk (iso format)

candidatekey: A Group of column which in combination can be used to identify each row individually

and Surrogate key:- A user defined key which will help uniquely identify each row where the application does not provide one.

Answer 3

Hi,

check the below link for a couple of excellent answers on all types of keys, one with SQL code (which columns can be selected as which key).

http://social.msdn.microsoft.com/Forums/en-us/sqlgetstarted/thread/27bd9c77-ec31-44f1-ab7f-bd2cb13129be

=========================================

Please mark as answer if helpful

Answer 4

You can follow this link for details about Keys in SQL Server

http://www.dotnet-tricks.com/Tutorial/sqlserver/GcF9120312-Different-Types-of-SQL-Keys.html