Could someone list out the difference between surrogate key n primary key?
I browsed through few sites and found out that Surrogate key is also used to identify a record by having an uniqueness wherein primary key is also used for the same purose. But am little confused about the usage of Surrogate key. Normally in a db, what for surrogate key is used?
How a particular column should be declared as Surrogate key?
Your primary key should uniquely identify each row of data. If you don't have column that you can use for the primary key, then you can create a surrogate.
A Surrogate is a column of values that have no relationship to the data. It has been created for the specific purpose of providing a unique value that can be used as the primary key.
e.g. assume you have this table
You can't create a primary key from the data because there is not a combination that will provide a unique value for each row. If you use Col1 then you have duplicate values, if you use Col2 you have duplicate values, if you use Col1+Col2 you also have duplicate values.
So, you need to create a surrogate key to use as the primary key. The surrogate is not related to the data, it only exists to give you a unique key.
ID Col1 Col2
-- ---- -----
01 John Smith
02 Alan Smith
03 John Jones
04 Mike Jones
05 Alan Smith
Hope this explains it better.
Everywhere's within walking distance if you have enough time.