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	BB@**.com
3	CCC	07029****17	CC2917	9535****81	CC@**.com
4	DDD	07029****18	DD2918	9535****82	DD@**.com
5	EEE	07029****19	EE2919	9535****83	EE@**.com

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.