Thursday, 10 July 2014

What is the difference between a primary key and a surrogate key?

More and more I read difference between Surrogated Key and PK, 
they both turned out to be PK only.. Can any one explain the 
difference between the both? 

Following is the explanation I have got after my research 
(Googling), but if you ask me after the explanation, so what is 
the difference than I may not be able to tell you... 

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). 

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.

No comments:

Post a Comment

intercompany PO multiple product receipt by x++

public void processStampICPO(PackingSlipId _deliveryNote,                             Transdate _deliverydate,                             ...