none
Surrogate Key vs Primary Key

Answers

  • A natural key is in common a field which is also used as data represent in the application, e.g. a article number.

    A surrogate key is just a e.g. sequentialy number build with IDENTITY function and it's only used internal in the database design, its a value which is not represented in the application GUI.

    The diiference: If you have a table with article and a article number field + surrogate key, you could change the article number without changing any foreign key; because they reference to the surrogate key.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Saturday, December 17, 2011 8:13 PM
    Moderator

All replies

  • Hello,

    I guess you mean more "Surrogate Key vs. Natural Key"; both can be primary key.

    See:

    Wiki. Natural Key
    Wiki Surrogate Key

    and Choosing a Primary Key: Natural or Surrogate?


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Saturday, December 17, 2011 7:48 PM
    Moderator
  • please post one main difference?
    Saturday, December 17, 2011 8:02 PM
  • A natural key is in common a field which is also used as data represent in the application, e.g. a article number.

    A surrogate key is just a e.g. sequentialy number build with IDENTITY function and it's only used internal in the database design, its a value which is not represented in the application GUI.

    The diiference: If you have a table with article and a article number field + surrogate key, you could change the article number without changing any foreign key; because they reference to the surrogate key.


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Saturday, December 17, 2011 8:13 PM
    Moderator
  • i didn't get it
    Saturday, December 17, 2011 8:18 PM
  •  

    Keys are natural if the attribute it represents is used for identification independently of the database schema.  What this basically means is that the keys are natural if people use them for "business", example: Invoice-Numbers, Tax-Ids, SSN etc.

     

    Surrogate keys are keys that have no “business” meaning and are solely used to identify a record in the table.  Such keys are either database generated (example: Identity, and Globally unique identifiers(GUIDs) in SQL Server) or system generated values (like generated via a table in the schema).

     


    Sharjeel Ashraf
    Sunday, December 18, 2011 2:19 AM
  • There is a good discussion (and many good links) referenced in the answers to this quiz

    http://beyondrelational.com/quiz/sqlserver/general/2010/questions/sqlserver-quiz-general-2010-madhu-k-nair-surrogate-key-vs-natural-key.aspx


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Sunday, December 18, 2011 3:35 AM
    Moderator
  • Hi All,

    I have a question that needs to be answered.

    The question is:-

    Surrogate Keys are NOT related to____________

    1) Performance

    2) History

    3) Storage Space

    4) Integer Type data

    As per my understanding, surrogate key deals with all the options above. How would I select one among those four options when I know that all those four options are directly or indirectly related to the concept of using surrogate keys? Can some one please enlighten me?

    Sunday, October 19, 2014 5:04 AM
  • Surrogate Keys are NOT related to_History_.

    In AdventureWorks Production.Product table,

    ProductID (INT) SURROGATE PK

    ProductNumber (varchar) NATURAL KEY --> should be set UNIQUE

    Name (varchar) CANDIDATE KEY  --> should be set UNIQUE

    rowguid (uniqueidentifier) ALTERNATE SURROGATE KEY --> should be set UNIQUE

    SELECT ProductID, ProductNumber, Name, rowguid, ListPrice
    FROM Production.Product;
    
    /*
    ProductID	ProductNumber	Name	rowguid	ListPrice
    512	RM-R800	HL Road Rim	CD9B5C44-FB31-4E0F-9905-3B2086966CC5	0.00
    513	RM-T801	Touring Rim	4852DB13-308A-4893-AAFA-390A0DFE9F12	0.00
    514	SA-M198	LL Mountain Seat Assembly	FCFC0A4F-4563-4E0B-BFF4-5DDCFE3A9273	133.34
    515	SA-M237	ML Mountain Seat Assembly	D3C8AE4C-A1BE-448D-BF58-6ECBF36AFA0B	147.14
    516	SA-M687	HL Mountain Seat Assembly	9E18ADAB-B9C7-45B1-BD95-1805EC4F297D	196.92
    */




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014




    Sunday, October 19, 2014 4:12 PM
    Moderator
  • Surrogate keys have no business relevance. They're included to ensure uniqueness and improve performance at the same time especially when natural or business key is composite.  Common way of implementing it is using an integer based IDENTITY fields to make sure storage space is made minimum.

    So from the above its obvious that the answer to your question is history.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, October 19, 2014 4:57 PM
    Moderator
  • My 2 cent :-)

    A surrogate key is a unique identifier, which can be arbitrary. The surrogate key is not derived from application data, or exist in the real world. Yet it can be used as data represent in the application. For example using NEWID(), or using Identity, in some cases timestamp/rowversion.

    A natural key is a key that is formed of attributes that exist in the real world. For example database name in the same instance, countries names, citizen's social number (actually any unique value which have meaning in the real world).

    * Both can be primary key as mentioned, Both can be use as data represent in the application.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    Sunday, October 19, 2014 6:26 PM
    Moderator
  • >A natural key is a key that is formed of attributes that exist in the real world. For example database name in the same instance, countries names, citizen's social number (actually any unique value which have meaning in the real world).

    Example: ProductNumber in AdventureWorks Production.Product is used in the real world: sales catalog.  Surrogate ProductID can be used but it would appear illogical, while Name would be too long for listing.




    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014




    Sunday, October 19, 2014 7:35 PM
    Moderator
  • Yes, I think the exclusion method works well here.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, October 21, 2014 3:52 PM
    Moderator