Different types of keys in RDBMS (SQL Server) ?

What is key in SQL server?
In the relational database, a key is single or combination of multiple columns from a table which can be used for unique identification of a particular record. 

Type of Keys in SQL server
There are following keys available In SQL server –
1. Super key
2. Candidate key
3. Primary key
4. Alternate key
5. Composite/Compound key
6. Unique key
7. Foreign key


Brief Explanation

Let’s take following table for the reference –
Id
Name
RollNo
CandidateId
Mobile
Email
1
AAA
07029****15
AA2915
9535****79
AA@**.com
2
BBB
07029****16
BB2916
9535****80
3
CCC
07029****17
CC2917
9535****81
4
DDD
07029****18
DD2918
9535****82
5
EEE
07029****19
EE2919
9535****83

Super key is the set of one or more columns from a table which can be used for unique identification of a record.
Example – From the above table, list of super keys can be [Id], [Id, Name], [Id, Name, RollNo], [Mobile] and … (Similar combinations).
  
Candidate key can be one or more columns from the super keys but it is smallest subset of the super key which can be used for unique identification of a record. 
Example – From the above list of super keys, some of the candidate keys can be [Id], [RollNo], [Mobile] and so on.

Primary key is one of the key from the set of candidate keys.
1. It can consider one or more columns from the table, Maximum limit is 16 columns
2. Primary key does not allow null and duplicate values
3. Only one primary key is possible in a table

Example – From the above list of candidate keys, some of the primary keys can be [Id], [RollNo], [Mobile] and so on.
It is good practice to use Id as a primary key as other information like Mobile, Email can be sensitive.

Alternate key is the key from list of candidate keys which is currently not a primary key, but it can be used for unique identification of a record.
Example – From the above list of candidate keys, If [Id] is a primary key then other candidate keys like [RollNo], [Mobile] can be alternate keys.

Composite/Compound key is the combination of more than one column from a table. Primary key or candidate keys with more than one column are known as Composite/Compound key.
Example – From the above list of candidate keys [Id, Name] , [Id, Name, RollNo] can be Composite/Compound keys.

Unique key is the combination of one or more than one column from a table. It is similar to Primary key but it allows only one null value with no duplicate record.
1. It can consider one or more columns from the table, Maximum limit is 16 columns
2. There can be more than one unique key in a table
Example – From the above table [RollNo], [CandidateId], [Mobile] and so on … can be few unique keys

Foreign key is the key which is used to maintain relationship between two tables. During connection one of the tables is called as master and other is called as a child table. Master table contains primary key while child table contains foreign key.
Example – Let’s say below master table is connected to the address table using primary key as Id.


Understanding about SQL keys is quite simple. I hope this article will be helpful. 

Please feel free to share your comments.

Post a Comment

12 Comments

  1. excellent post.......................

    ReplyDelete
  2. I'm sorry to say actually i checked in mysql RDBMS not in MSSQL RDBMS.Yet i think concept is same in both that's why i'm posting my problem.My problem is in unique key field value can accept multiple NULL values.In my minds definition of the unique key violates.Actually what i'm wrong i don't understand.Please help me.

    ReplyDelete
    Replies
    1. Pulaha, Unique key column can have single null value, It does not accept multiple null values.

      But you can have some alternative way to achieve it. Check the below link i believe it will help you -
      http://decipherinfosys.wordpress.com/2007/11/30/multiple-null-values-in-a-unique-index-in-sql-serverdb2-luw/

      Delete
  3. Thank you so much .. It's a nice explanation there.Now i got clear concept about unique key.

    ReplyDelete
  4. very helpful..thanks

    ReplyDelete
  5. Very nice article.. Helped me a lott...

    ReplyDelete
  6. Sort of confused.....
    Primary key is one of the key from the set of candidate keys.
    1. It can consider one or more columns from the table, Maximum limit is 16 columns

    3. Only one primary key is possible in a table
    Are not point 1 & 3 contradictory ? Point 1 is also present under Unique key.

    ReplyDelete
    Replies
    1. Nikhil- Only one primary key is possible in a table(point- 3) but it can contain multiple columns from the same table(point- 1), So both statement are correct.

      And also Same this for unique key.

      Delete
  7. Superb Bro...Excellent Hats off

    ReplyDelete
  8. awesome post..its crystal clear

    ReplyDelete