Keys in RDBMS

We know that database uses tables to organize information. To maintain data integrity (that is data should be correct and in well formed) we use concept of keys. There are five types of keys in database which is as follows -

  • Candidate key
  • Primary Key
  • Foreign Key
  • Alternate Key
  • Composite Key

Example:

STUDENT {SID, FNAME, LNAME, COURSEID}

Here in STUDENT table keys are:

Candidate keys are SID or FNAME+LAME

Primary Key: SID

Foreign Key: COURSEID

Alternate Key:  FNAME+LAME

Composite Key:  FNAME+LAME

Candidate Key
Candidate keys are those keys which is candidate for primary key of a table. In simple words we can understand that such type of keys which full fill all the requirements of primary key which is not null and have unique records is a candidate for primary key. So thus type of key is known as candidate key. Every table must have at least one candidate key but at the same time can have several.

Primary Key
Such type of candidate key which is chosen as a primary key for table is known as primary key. Primary keys are used to identify tables. There is only one primary key per table. In SQL Server when we create primary key to any table then a clustered index is automatically created to that column.

Foreign Key
Foreign key are those keys which is used to define relationship between two tables. When we want to implement relationship between two tables then we use concept of foreign key. It is also known as referential integrity. We can create more than one foreign key per table.  foreign key is generally a primary key from one table that appears as a field in another where the first table has a relationship to the second. In other words, if we had a table A with a primary key X that linked to a table B where X was a field in B, then X would be a foreign key in B.

Alternate Key
If any table have more than one candidate key, then after choosing primary key from those candidate key, rest of candidate keys are known as an alternate key of  that table. Like here we can take a very simple example to understand the concept of alternate key. Suppose we have a table named Employee which has two columns EmpID and EmpMail, both have not null attributes and unique value. So both columns are treated as candidate key. Now we make EmpID as a primary key to that table then EmpMail is known as alternate key.

Composite Key
When we create keys on more than one column then that key is known as composite key. Like here we can take an example to understand this feature. I have a table Student which has two columns Sid and SrefNo and we make primary key on these two column. Then this key is known as composite key.

42 Thoughts on “Keys in RDBMS

  1. Chandra on February 11, 2015 at 9:54 pm said:

    Very useful. Clearly explained.

  2. Upto This time i am confused in the difference between keys but now its cleared with this documenntation

  3. unknow on March 24, 2015 at 12:59 am said:

    Hi
    such a lovely way to represent a love with SQL Concept. I like it……

  4. nomesh on April 2, 2015 at 1:02 pm said:

    Thanks for this sweet and short answers

  5. Maryam Maghrebi on April 28, 2015 at 1:25 pm said:

    That was very helpful. Thanks so much

  6. greens on June 9, 2015 at 12:04 am said:

    hi very nice ,clean explanation. very easy and very quick to understand ! thanx budddy :))

  7. lakshmi nair on July 31, 2015 at 12:30 pm said:

    Good explanation.. Thanks, it helped me to prepare for my interview

  8. Naveen konda on August 4, 2015 at 11:49 am said:

    I have big confusion of keys before reading this website. nice document. thank u “crazyforcode”.

  9. shalini on August 19, 2015 at 8:47 pm said:

    can anyone explain what is superkey??

  10. U explained it in a very simple and easy to understand lang. Thnk u very much

  11. well explained but plz update your types of keys bcoz their are 7 types of keys…..
    super key
    unique key

  12. useful piece of knowledge
    helped a lot to understand the difference between the keys……..

  13. shanthana bharathi on November 12, 2015 at 11:55 am said:

    i’m little bit confused and i’m not getting it …

  14. Dipankar on November 18, 2015 at 9:57 am said:

    Great work !!

  15. m.z. khan on November 26, 2015 at 7:42 pm said:

    It is very use full for me to find about info for SQL

  16. very informative it helped me alot,,,thank U

  17. Mohd Riyaz on December 11, 2015 at 2:44 pm said:

    That was very helpful. Thanks so much

  18. Sebastin on February 4, 2016 at 7:38 pm said:

    Thank You !!!
    Very good Explanation…
    Now i understood that difference…..

  19. Pratiba Kanswal on March 22, 2016 at 12:48 pm said:

    Nice article,i really understand now this keys clearly.

    Thanks a lot….

  20. Tell me about Super Key !!!!!!! plz

  21. Damanpreet Singh on April 26, 2016 at 8:11 am said:

    Thnks Its too easy language to learn

  22. Vinay Kumar on May 6, 2016 at 10:02 am said:

    Nice answer

  23. Netrapal Rajput on May 6, 2016 at 3:19 pm said:

    Good,These article is very understandable ,
    I was confused , composit key and candidate key , But now I have clear

    Thanks

  24. Arun.D on May 20, 2016 at 9:42 am said:

    Good explanation

  25. lizee on July 27, 2016 at 11:57 am said:

    Thanks a bunch for this post it really helped me understand the keys better

  26. amit parmar on August 3, 2016 at 2:30 pm said:

    Thanks a lot.You have explained in a fantastic way so anyone can understand it in a better way.

  27. Its very helpfully to my study. Can you give an example of each key / please….!!!!!

  28. really nice explanation..Thanks a lot ..

  29. Thank u so much.. Very good explanation. Simple and useful.

  30. SQL Composite Key

    A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, but when it taken individually it does not guarantee uniqueness.

  31. Ankit solanki on November 7, 2016 at 7:19 pm said:

    really helpful,
    thanks man , thanks a bunch…………..

  32. very nice,thanks it really help me

  33. Narayan on December 15, 2016 at 3:48 pm said:

    Nice article, apart from the keys mentioned here ( Candidate key, Primary Key, Foreign Key, Alternate Key, Composite Key), there are additional keys

    Natural key – The key which existed in the real world and is used in business purpose for identifying a record. Example – Bank account number; it is quite “natural” that when a customer walks to his bank, he prefers this details to be identified by account number rather than is customer id. The natural key may or may not be the primary key.

    Surrogate key – It is the system generated candidate key, often made a primary key.

    Shall we make the natural key or Surrogate key as primary key? Both are candidate keys.

    The disadvantage with natural key is that it its exposed to the business, hence at times there is request to change it. Hence designing database based on natural key as primary key is not a sustainable design.

    If surrogate key is considered as primary key, then the design is sustainable. Business will never ask to change the surrogate key because this value is not shared with business. The slight disadvantage is that there is an additional column of surrogate key in the table. The advantage of sustainable design overpowers the dis-advantage. So now a days, systems are designed as surrogate key as primary key.

  34. itazya on March 26, 2017 at 3:27 am said:

    Wow some short and well explained answers. I like it

  35. Bhavesh Jadav on July 19, 2017 at 2:04 pm said:

    It is very useful for me, thanks…

  36. Thanks for making my topic clear.

  37. Iris mata on March 1, 2018 at 11:32 pm said:

    Awesome……

  38. Carley on March 28, 2018 at 9:51 am said:

    This paragraph will help the internet viewers for creating new website or
    even a weblog from start to end.

  39. Affaan on July 17, 2018 at 5:15 pm said:

    Primary key definition not satisfiable

  40. yaseen on July 19, 2018 at 7:25 pm said:

    nice article sir

  41. Mandeep chatha on September 12, 2018 at 2:13 pm said:

    Good but not so.Can do better….

Leave a Reply to unknow Cancel reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Post Navigation