What is the difference between surrogate key and primary key in sql server ? [Resolved]

Posted by Sriharim under Sql Server on 5/9/2015 | Points: 10 | Views : 452 | Status : [Member] | Replies : 5
What is surrogate key ?
What is the difference between surrogate key and primary key in sql server ?

---
Srihari



Responses

Posted by: Bandi on: 5/11/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
Primary Key:
--> uniquely identifies a row in a table
--> May use one or more columns
--> values can be modified

Surrogate key:
--> Must use only one column
--> Values are uniquely generated
--> cannot be modified

A surrogate key is a primary key. Paul meant that a surrogate key is one kind of primary key. All surrogate keys are primary keys. Not all primary keys are surrogate keys.

A surrogate key is somthing that has no meaning other than uniquely identifying the row.
Because it has no meaning, there will never be any reason to change it, which is a very desirable quality in a primary key.

surrogate keys are integers that are assigned sequentially as needed to populate a dimension table.
refer
http://blog.sojib2bd.com/oracle-10g/what-is-the-difference-between-natural-key-and-surrogate-key.xhtml

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Kumarmaddu on: 5/11/2015 [Member] Starter | Points: 25

Up
0
Down
Hi
Primary Key:
A primary key is a special constraint on a column or set of columns. A primary key constraint ensures that the column(s) so designated have no NULL values, and that every value is unique. Physically, a primary key is implemented by the database system using a unique index, and all the columns in the primary key must have been declared NOT NULL. A table may have only one primary key, but it may be composite (consist of more than one column).
Surrogate key:
A surrogate key is any column or set of columns that can be declared as the primary key instead of a "real" or natural key. Sometimes there can be several natural keys that could be declared as the primary key, and these are all called candidate keys. So a surrogate is a candidate key. A table could actually have more than one surrogate key, although this would be unusual. The most common type of surrogate key is an incrementing integer, such as an auto_increment column in MySQL, or a sequence in Oracle, or an identity column in SQL Server.


Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriharim on: 5/11/2015 [Member] Starter | Points: 25

Up
0
Down
Thank you for your response,

Please clarify on Natural Key,

Natural Key is a uniquely identifies a row in a table and May use one or more columns is a column.

Natural key can be achieved/implement by Primary Key and Unique Key ? is there any other ways to create natural key ?

Does All Unique Keys are Natural Keys ? and All primary keys are Natural Key ?

Bussiness Key and Natural Key are both same ?


---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Amatya on: 5/11/2015 [Member] Bronze | Points: 25

Up
0
Down
Bandi has cleared all the doubts... Its fantastic explanation.

Feel free to share informations 9731764134
Thanks

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sriharim on: 5/12/2015 [Member] Starter | Points: 25

Up
0
Down
Please clarify on Natural Key,

Natural Key is a uniquely identifies a row in a table and May use one or more columns is a column.

Natural key can be achieved/implement by Primary Key and Unique Key ? is there any other ways to create natural key ?

Does All Unique Keys are Natural Keys ? and All primary keys are Natural Key ?

Bussiness Key and Natural Key are both same ?

---
Srihari

Sriharim, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response