Different Types of SQL Keys

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.

We are defining here a relational database having 2 tables:

Employee Table

Department Table

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.

Read More - DBMS Interview Questions For Freshers

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)

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

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.

Take our Sqlserver skill challenge to evaluate yourself!

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

GET FREE CHALLENGE