none
What is surrogate key, candidate key and tuple in RDBMS RRS feed

  • Question

  • please tell me with a example that what is surrogate key, candidate key and tuple in RDBMS ?

    thanks

    Friday, December 2, 2016 1:56 PM

Answers

  • A Surrogate or Candidate key is a column in a table that uniquely identifies a row, but it not the business key for the row. It can be a combination of columns which is usually called a candidate key. I do not see more than one column in a table as a key much any more.

    The surrogate is a integer data type and used as a primary key to be foreign keys in related tables in order to speed up joins. If the Business Key is like the character data type example below, the column width is large and with millions of rows in order detail tables to join, can become a performance problem.

    Also, if the Product Code changes and there are foreign keys to the product code as the primary key, the related tables have to be updated with the change. This can be a maintenance performance problem.

    Example)

    Product Surrogate Key - sequential Number like 1, 2, 3, 4, etc.

    Product Business Key - Product Code 'AB-0123' or 'MountBikeB10'


    Thomas LeBlanc twitter ( @TheSmilingDBA )

    • Proposed as answer by Hilary CotterMVP Friday, December 2, 2016 2:50 PM
    • Marked as answer by Sudip_inn Monday, December 5, 2016 9:33 AM
    Friday, December 2, 2016 2:25 PM
  • To add to what Thomas wrote, a candidate key is any key (or superkey) that identifies uniquely a tuple in a relation.

    A tuple is a list of values in a relation, which corresponds to a row in a table in the physical implementation of the relational model.

    You can easily look up any of these terms in Google.

    • Proposed as answer by Hilary CotterMVP Friday, December 2, 2016 2:50 PM
    • Marked as answer by Sudip_inn Monday, December 5, 2016 9:33 AM
    Friday, December 2, 2016 2:38 PM
  • Candidate Key – It can be defined as minimal Super Key or irreducible Super Key. In other words an attribute or a combination of attribute that identifies the record uniquely but none of its proper subsets can identify the records uniquely.

    E.g. of Candidate Key

    1. ID
    2. Name, Address

    Surrogate keys are keys that have no business meaning and are solely used to identify a record in the table.

     Row & Tuple are same thing, and record is also same.


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    • Marked as answer by Sudip_inn Monday, December 5, 2016 9:33 AM
    Friday, December 2, 2016 2:53 PM

All replies

  • A Surrogate or Candidate key is a column in a table that uniquely identifies a row, but it not the business key for the row. It can be a combination of columns which is usually called a candidate key. I do not see more than one column in a table as a key much any more.

    The surrogate is a integer data type and used as a primary key to be foreign keys in related tables in order to speed up joins. If the Business Key is like the character data type example below, the column width is large and with millions of rows in order detail tables to join, can become a performance problem.

    Also, if the Product Code changes and there are foreign keys to the product code as the primary key, the related tables have to be updated with the change. This can be a maintenance performance problem.

    Example)

    Product Surrogate Key - sequential Number like 1, 2, 3, 4, etc.

    Product Business Key - Product Code 'AB-0123' or 'MountBikeB10'


    Thomas LeBlanc twitter ( @TheSmilingDBA )

    • Proposed as answer by Hilary CotterMVP Friday, December 2, 2016 2:50 PM
    • Marked as answer by Sudip_inn Monday, December 5, 2016 9:33 AM
    Friday, December 2, 2016 2:25 PM
  • To add to what Thomas wrote, a candidate key is any key (or superkey) that identifies uniquely a tuple in a relation.

    A tuple is a list of values in a relation, which corresponds to a row in a table in the physical implementation of the relational model.

    You can easily look up any of these terms in Google.

    • Proposed as answer by Hilary CotterMVP Friday, December 2, 2016 2:50 PM
    • Marked as answer by Sudip_inn Monday, December 5, 2016 9:33 AM
    Friday, December 2, 2016 2:38 PM
  • u said :- A Surrogate or Candidate key is a column in a table that uniquely identifies a row, but it not the business key for the row. It can be a combination of columns which is usually called a candidate key. I do not see more than one column in a table as a key much any more.

    mostly PK is used to uniquely identifies a row. so PK can be called Surrogate or Candidate key ?

    just do not understand "but it not the business key for the row" what is business key. i never heard this term business key.

    candidate key means composite key ?

    Friday, December 2, 2016 2:48 PM
  • Candidate Key – It can be defined as minimal Super Key or irreducible Super Key. In other words an attribute or a combination of attribute that identifies the record uniquely but none of its proper subsets can identify the records uniquely.

    E.g. of Candidate Key

    1. ID
    2. Name, Address

    Surrogate keys are keys that have no business meaning and are solely used to identify a record in the table.

     Row & Tuple are same thing, and record is also same.


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    • Marked as answer by Sudip_inn Monday, December 5, 2016 9:33 AM
    Friday, December 2, 2016 2:53 PM
  • just do not understand "but it not the business key for the row" what is business key. i never heard this term business key.

    A business key is something which is used to identify the entity in real life. One example is IN as the country code for India.


    A more commonly used name is "natural key"´.
    Friday, December 2, 2016 10:11 PM
  • u said

    E.g. of Candidate Key

    1. ID
    2. Name, Address

    so which one is Candidate key here ?

    what is surrogate key too....give me a example for surrogate key ?

    thanks

    Monday, December 5, 2016 9:33 AM
  • To be honest I never use the term "candidate key" in real life. I know that the term occurs in Date's book which I read many years ago. I guess it's column or set of columns that might be a key, but we don't know this for sure yet.

    A surrogate key is key which has no relation whatsoever to the real-world data. Typically this is an integer or a guid which is determined when you store the data. All IDENTITY column you see around you are surrogate keys, as are all other keys generated inside the database.

    Monday, December 5, 2016 10:33 PM