Holi Sale. Get upto 40% OFF on Job-oriented Training! Offer Ending in
D
H
M
S
Get Now
Browse Tutorials
Different Types of SQL Keys

Different Types of SQL Keys

14 Mar 2024
Beginner
188K Views
11 min read

SQL Keys: An Overview

SQL Key is a single or combination of multiple fields in a table. It is used to fetch or retrieve records/data rows from the data table according to the condition/requirement. Keys are also used to create a relationship among different database tables or views. In this SQL Server tutorial, we'll understand different SQL Keys and their application.

What is a key in SQL?

In SQL, the different types of keys are the set of attributes used to identify a specific row from a table and to find or create the relation between two or more tables. For example, the keys identify the rows by combining one or more sets of columns. SQL provides different types of keys such as super key, candidate key, etc. Generally, all the SQL keys use constraints to uniquely identify the rows from the larger set of datasets.

Read More: SQL Server Interview Questions and Answers

Types of SQL Keys

We have the following types of keys in SQL which are used to fetch records from tables and to make relationships among tables or views.

Types of SQL Keys

We are defining here a relational database having 2 tables:

Employee Table

Emp_IdEmp_NameAddressMobile_NoEmail
1SakshiDelhi123456789abc@xyz.com
2SouravHazaribagh223365796jkl@gmail.com
3PragatiNanded175468965ghi@yahoo.com

Department Table

Emp_IdDept_NameDesignation
1VideoVideo_Maker
2Search_EngineSEO
3ContentWriter

We'll use these tables to understand the various keys.

1. Candidate Key

A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as a Primary Key.

The value for the Candidate key is always unique and non-null for all the tuples types. One thing to be remembered is that every table has to have at least one Candidate key, but there can be more than one candidate key can be there in a table. It is a super key with no repeated data is called a candidate key.

Example

Emp_Id is the candidate key for relation Employee.

2. Primary Key

A primary key is a set of one or more fields/columns of a table that uniquely identify a record in a database table. It can not accept null, or duplicate values.

  • Only one Candidate Key can be the Primary Key.
  • Out of all the candidate keys that can be possible or created for the specific table, there should be only one key that can be used to retrieve unique tuples from the table.
  • It is a thumb rule that there can be one Primary key that should be there for a table.
  • It can identify only one tuple (a record) at a time.

Example

Emp_Id is the primary key for relation Employee.

3. Super Key

A super key is a set of one or more than one key that can be used to identify a record uniquely in a table. Example: Primary key, Unique key, and Alternate key are a subset of Super Keys.

Super Key can contain multiple attributes that might not be able to identify tuples in a table independently, but when grouped with certain keys, they can identify tuples uniquely. It supports NULL values.

  • Adding zero or more attributes to the candidate key generates the super key.
  • A candidate key is a super key but vice versa is not true.

Example

Emp_Id + Mobile_No is a super key.

4. Alternate key

An Alternate key is a key that can work as a primary key. It is a candidate key that currently is not a primary key. It is also called a secondary key.

In other words, the alternate key is a column or collection of columns in a table that can uniquely identify each row in that table. Every table of the database table can have multiple options for a primary key to be configured but out of them, only one column can be set as the primary key. All the keys which are not primary keys are called the alternate keys of that table.

It maintains data integrity by providing a backup option for uniquely identifying records. While the Primary Key is the direct path to distinctiveness, the Alternate Key is a secondary assurance. This secondary assurance becomes crucial when dealing with scenarios where the primary key might not be feasible due to complexity, length, or other considerations.

Alternate keys are very flexible lies in its flexibility. It caters to scenarios where different parts of the database's user base or application require unique identification using other attributes.

Example

In the Employee relation, Emp_Id, as well as Mobile_No both, are candidate keys but Mobile_No will be an alternate key (only one out of many candidate keys)

Alternate key

5. Composite/Compound Key

A composite Key is a combination of more than one field/column of a table. It can be a Candidate key or a primary key.

It gets its importance when it's not possible for a single column/attribute to uniquely identify all the records of a table. It acts as a primary key if there is no primary key in a table.

To create a composite key, you need to define multiple columns as primary or unique. This intertwining of attributes forms a robust identifier that prevents identical combinations from surfacing.

6. Unique Key

Unique Key can be a column or set of columns that can be used to uniquely identify the tuple from the database. One or more fields can be declared as a unique Key. The unique Key column can also hold the NULL value. The use of unique keys improves the performance of data retrieval. It makes searching for records from the database much faster & efficient.

Example

In the Employee relation, a Unique Key could be applied to the email column, allowing null values to be present while maintaining the uniqueness requirement for non-null entries.

Read More: Difference between Primary Key and Unique Key

7. Foreign Key

A foreign key is an attribute that is a Primary key in its parent table but is included as an attribute in another host table. The relation that is being referenced is called the referenced relation and the corresponding attribute is called the referenced attribute. The relation that refers to the referenced relation is called a referencing relation and the corresponding attribute is called a referencing attribute. The referenced attribute of the referenced relation should be the primary key to it.

Foreign Key may have duplicate & NULL values if it is defined to accept NULL values.

Example

Emp_Id in the Department table is a foreign key to Emp_Id in Employee relation.

Read More: Difference between primary key and foreign key

Difference between different Keys in SQL

Key type Purpose Characteristics
Primary KeyUsed to uniquely identify a row in a tableCannot be NULL, and must be a unique one per table
Foreign KeyUsed to maintain referential integrity between tables It can be NULL
Composite Key Used to uniquely identify a row when a single column is not sufficientIt is a combination of columns, however, they must be unique.
Unique KeyUsed to prevent duplicate values in a column It can be NULL
Candidate KeyUsed to identify potential Primary KeysIt can be unique and can uniquely identify each row in a table
Super KeyUsed to uniquely identify rows in a broad senseIt can contain additional non-unique columns

Defining Keys in SQL Server


--Department Table
 CREATE TABLE Department 
(
 DeptID int PRIMARY KEY, --primary key
 Name varchar (50) NOT NULL,
 Address varchar (200) NOT NULL
 ) 
--Student Table
CREATE TABLE Student 
(
 ID int PRIMARY KEY, --primary key
 RollNo varchar(10) NOT NULL,
 Name varchar(50) NOT NULL,
 EnrollNo varchar(50) UNIQUE, --unique key
 Address varchar(200) NOT NULL,
 DeptID int FOREIGN KEY REFERENCES Department(DeptID) --foreign key
) 

Practically in the database, we have only three types of keys Primary Key, Unique Key, and Foreign Key. Other types of keys are the only concepts of RDBMS that you should know.

Read More: Basics of SQL Commands

What do you think?

SQL Keys are one of the widely used attributes of the relational database management system which plays an important role in establishing a concrete relationship between two or more tables at a time. In this article, I explained the different types of keys in SQL Server with an example. I hope after reading this article you will get a clear understanding of it. For practical experience, consider our SQL Server Course.

FAQs

Q1. What is a Candidate Key in SQL?

A Candidate Key is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as a Primary Key.

Q2. Why Keys are necessary for DBMS?

Keys help to fetch or retrieve records/data rows from the data table according to the condition/requirement. Keys are also used to create a relationship among different database tables or views.

Q3. How many types of keys are there in MySql?

There are seven types of keys in MySql.

Q4. What are the differences between Primary Key and Candidate Key in SQL?

Primary keys must have unique values and cannot contain NULL values, whereas candidate keys may contain NULL values.

Q5. What is a Unique Key?

A unique Key can be a column or set of columns that uniquely identifies the tuple from the database.

Take our free sqlserver skill challenge to evaluate your skill

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET CHALLENGE

Share Article
About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this