Different Types of SQL Keys

Print 6 min read

31 Aug 2022

Beginner

138K Views

A 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 SQL, the different types of keys are the set of attributes that can be used to identify the 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 set of columns. SQL provides different types of keys such as super key, candidate key, primary key, foreign key, alternate key, compound key, composite key, and so on. generally, all the SQL keys use constraints to uniquely identify the rows from the larger set of datasets.

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.

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, Alternate key are a subset of Super Keys.
The super key in SQL can be defined as a set of one or more than one key that can be used to identify a certain type of data in a table. This key includes only those fields which include a unique value.
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.
A Candidate key is a subset of Super keys that we have discussed previously and it is lacking any unnecessary attributes that are not important to be used uniquely identify tuples of a table. 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.

Example: In the below diagram ID, RollNo and EnrollNo are Candidate Keys since all these three fields can work as Primary Key.
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, duplicate values. Only one Candidate Key can be 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. This candidate key is called the "Primary Key". It is a thumb rule that there can be one Primary key that should be there for a table. Depending on how the candidate key is constructed, the primary key possibly can be a single attribute or a group of attributes. But the crucial point to remember is that the created Primary key should be unique and without the NULL attribute.
Alternate key
An Alternate key is a key that can work as a primary key. Basically, it is a candidate key that currently is not a primary 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.
Example: In the below diagram RollNo and EnrollNo become Alternate Keys when we define ID as the Primary Key.
Composite/Compound Key
A composite Key is a combination of more than one field/column of a table. It can be a Candidate key, Primary key.
Unique Key
A unique key is a set of one or more fields/columns of a table that uniquely identify a record in a database table. It is like a Primary key but it can accept only one null value and it can not have duplicate values. For more help refer to the article Difference between primary key and unique key.
Foreign Key
Foreign Key is a field in a database table that is the Primary key in another table. It can accept multiple nulls and duplicate values. For more help refer to the article Difference between primary key and foreign key.

Example: We can have a DeptID column in the Employee table which is pointing to a DeptID column in a department table where it is a primary key.

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
)

Important Note

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.

What do you think?

SQL Keys are one of the widely used attributes of the relational database management system. which plays an important role to establish a concrete relationship between two or more multiple tables at a time. Keys are also helpful queries to execute faster i.e. the retrieval of the records from the database becomes much faster by using Keys. It also set the different constraints to uniquely identify the tuples from the large set of data.

In this article, I explained the different types of keys in SQL Server with an example. I hope after reading this article you will be aware of the keys in SQL Server. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.