Different Kinds of Keys in DBMS

14 min readSep 20, 2023

In a Database Management System (DBMS), keys play a fundamental role in ensuring the integrity, organization, and efficient retrieval of data. Keys are essentially attributes or combinations of attributes that uniquely identify records within a database table. They help maintain data consistency and establish relationships between tables, facilitating data retrieval and manipulation.

There are some types of Keys in DBMS……… Such as,

Primary Key

A primary key is a field or set of fields in a database table that uniquely identifies each record in the table. It serves as a unique identifier for each row and ensures that each record can be uniquely identified and retrieved. It ensures that no two records have the same value for this key attribute(s). Typically, primary keys are used for fast data retrieval and are often automatically indexed by the DBMS for quick access.

Here are some key characteristics and points about primary keys:

Uniqueness :- Every value in the primary key must be unique for each record in the table. No two records can have the same primary key value.
Non-null :- The primary key value cannot be NULL. Every record in the table must have a valid, non-null primary key value.
Fixed :- The primary key should be a stable value that does not change over time. It provides a constant reference to a specific record.
Single or Composite :- A primary key can be a single field or a combination of fields. In the case of multiple fields, it is referred to as a composite primary key.
Indexed :- The primary key is often automatically indexed by the database management system (DBMS) to enhance the retrieval performance.
Used in Relationships :- Primary keys are often used to establish relationships between tables in a relational database. In a related table, the primary key from one table is used as a foreign key in another table to create a link between them.

For an example :- In a table of employees, a unique employee ID could be used as the primary key. In a composite key scenario, a combination of fields such as (department ID, employee ID) might be used.

Here’s an example of creating a table with a primary key in SQL

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DepartmentID INT
);

In this example, EmployeeID is the primary key for the Employees table.

A primary key’s main features are :-

It must contain a unique value for each row of data.
It cannot contain null values.
Every row must have a primary key value.

Foreign Key

A foreign key is a field in a relational database table that is used to establish a link between two tables. It creates a relationship between the tables by referencing the primary key of another table. The purpose of a foreign key is to ensure referential integrity in the database, meaning that relationships between tables are consistent and that data remains accurate and reliable. Foreign keys are crucial for maintaining data consistency and ensuring that related data is linked correctly.

Here are some key characteristics and points about foreign keys:

Reference to a Primary Key :- A foreign key refers to the primary key of another table. This establishes a connection between the two tables, indicating that the values in the foreign key column(s) of one table correspond to the values in the primary key column(s) of another.
Ensures Referential Integrity :- The foreign key relationship helps maintain referential integrity by ensuring that every foreign key value in a table corresponds to a valid primary key value in another table. This helps prevent orphaned or inconsistent data.
Uniqueness or Null Values :- The values in a foreign key column either must be unique or can contain null values, depending on the specific requirements and constraints defined during table creation.
Constraint :- A foreign key is a type of constraint that is applied to a column or columns in a table. This constraint defines the relationship between tables and imposes rules to maintain consistency.
CASCADE Options :- Some database management systems allow the specification of “CASCADE” options, such as CASCADE DELETE or CASCADE UPDATE. These options determine the actions to be taken when a referenced primary key is deleted or updated.
Multiplicity :- Foreign keys can have different levels of multiplicity, such as one-to-one, one-to-many, or many-to-many relationships. This depends on the nature of the relationship between the tables.
Syntax in Table Creation :- In SQL, the syntax for defining a foreign key is typically included in the CREATE TABLE statement. It involves specifying the foreign key column(s) and the referenced primary key.

Here’s a simplified example in SQL:

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
ProductID INT,
Quantity INT,
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

In this example, the Orders table has a foreign key ProductID that references the ProductID column in the Products table. This establishes a relationship between the two tables based on the ProductID values.

Super Key

A super key is a set of one or more attributes (columns) that, taken collectively, can uniquely identify a record in a database table. In other words, it’s a set of columns that can uniquely identify each row in a table. It may contain more attributes than necessary for uniqueness and can include the primary key.

Here are some key characteristics and points about Super keys:

Uniqueness :- A super key must have the property that no two distinct rows in a table have the same combination of values for all the columns in the super key.
Minimality :- A super key should be minimal, meaning that no proper subset of the super key should have the uniqueness property. Removing any column from the super key would result in a loss of the unique identification property.
Candidate Key :- Every candidate key is a super key, but not every super key is a candidate key. A candidate key is a super key that is irreducible, meaning it has no unnecessary attributes and removing any attribute from it would cause it to lose the uniqueness property.
Composite Super Key :- A super key can be a single attribute or a combination of multiple attributes. A composite super key involves a combination of two or more columns to uniquely identify a record.
Used in Database Design :- Super keys are relevant in the process of database design and normalization. They help in identifying potential candidate keys and aid in designing tables with minimal redundancy.

For an Example :- In a table of students, a super key could be the combination of (StudentID, Email), assuming that no two students share the same combination of StudentID and Email.

Here’s a simple example to illustrate a super key :-

CREATE TABLE Employees (
EmployeeID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100),
PRIMARY KEY (EmployeeID),
UNIQUE (EmployeeID, Email) — This combination is a super key
);

In this example, the combination of (EmployeeID, Email) forms a super key for the Employees table. It ensures uniqueness, as no two records in the table can have the same combination of EmployeeID and Email.

To find the Super Keys for a table :
We can find the maximum value of a super key based on it’s column number. It can find (2^n -1) this equation. In here, n is number of columns.

Here, We have to create several Super Keys.

SID
SID, Name
SID, Marks
SID, Department
SID, Course
SID, Name, Marks
SID, Name, Department
SID, Name, Course
SID, Marks, Department
SID, Marks, Course
SID, Department, Course
SID, Name, Marks, Department
SID, Name, Marks, Course
SID, Marks, Department, Course
SID, Name, Marks, Department, Course
SID, Name, Department, Course
Name, Marks, Department, Course
Marks, Department, Course

Composite Key

A composite key is a type of key in a relational database that consists of two or more columns (attributes) in a table, working together to uniquely identify each record in that table. Unlike a single-column primary key, which is composed of only one attribute, a composite key involves a combination of multiple attributes. It is often used when no single attribute can guarantee uniqueness, but the combination of several attributes does.

Here are some key characteristics and points about Composite keys:

Uniqueness :- The combination of values in the columns that make up the composite key must be unique for each row in the table.
Primary Key or Alternate Key :- A composite key can serve as the primary key for a table if it uniquely identifies each record. Alternatively, it can be an alternate key if the primary key is a different column or set of columns.
Use in Relationships :- Composite keys are often used in relationships between tables. Columns from a composite key in one table may be used as foreign keys in another table to establish a link between them.
Example :- In a table of orders, a composite key could be formed by combining the columns (OrderID, ProductID) to uniquely identify each order line.
CREATE TABLE OrderLines (
OrderID INT,
ProductID INT,
Quantity INT,
PRIMARY KEY (OrderID, ProductID),
FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
);
Redundancy Reduction :- The use of composite keys can help reduce redundancy by capturing more complex relationships between entities without the need for additional tables.
Querying Considerations :- When querying tables with composite keys, it’s essential to use all the columns in the key to uniquely identify records. Queries that involve a subset of the columns in the composite key may not be sufficient for uniquely identifying records.
Syntax in Table Creation :- In SQL, the syntax for creating a table with a composite key involves listing the columns within the PRIMARY KEY constraint.
Here’s an example of creating a table with a composite key in SQL :-
CREATE TABLE StudentCourses (
StudentID INT,
CourseID INT,
Grade CHAR(2),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
Before In this example, the combination of (StudentID, CourseID) forms a composite key for the StudentCourses table.

Alternate Key

An alternate key, also known as a secondary key, is a candidate key that is not selected as the primary key for a table. A candidate key is a set of one or more columns in a table that can uniquely identify a record. While a table can have multiple candidate keys, one of them is chosen to be the primary key, and the others become alternate keys. Alternate keys can be used for unique identification as well.

Here are some key characteristics and points about Alternate keys:

Uniqueness :- Like the primary key, an alternate key must ensure uniqueness. The combination of values in the alternate key columns should be unique for each record in the table.
Candidate Key :- An alternate key is essentially a candidate key that is not chosen as the primary key. It remains a unique identifier for records in the table.
Used for Relationships :- Alternate keys are often used in relationships between tables. Columns from an alternate key in one table may be used as foreign keys in another table to establish connections.
Enforcing Uniqueness :- Even though an alternate key is not the primary key, the database management system (DBMS) enforces uniqueness for the columns that make up the alternate key.
Example :- In a table of employees, if both Social Security Number (SSN) and Employee ID are candidate keys, one of them may be chosen as the primary key, and the other becomes an alternate key.
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
SSN VARCHAR(11) UNIQUE,
FirstName VARCHAR(50),
LastName VARCHAR(50)
);
In this example, SSN is an alternate key.
Data Integrity :- Alternate keys contribute to data integrity by ensuring that no two records in the table have the same combination of values in the alternate key columns.
Querying and Indexing :- Depending on the use cases and query patterns, alternate keys might be indexed to improve query performance, especially when they are used in JOIN operations.
Notation :- In data modeling, alternate keys are sometimes denoted with a dashed underline to distinguish them from the primary key.

It’s worth noting that while the primary key uniquely identifies records and is often used in foreign key relationships, alternate keys provide additional ways to uniquely identify records and may be useful in certain querying scenarios or for specific business requirements.

Unique Key

A unique key is a database constraint that ensures that all values in a column or a set of columns are unique across the rows in a table. While it’s similar to a primary key in terms of enforcing uniqueness, a unique key differs in that it allows for at least one NULL value. Unique keys help maintain data integrity by preventing duplicate values in the specified column(s).

Here are some key characteristics and points about Unique keys:

Uniqueness :- Like the primary key, a unique key ensures that the values in the specified column or columns are unique. No two rows can have the same combination of values.
Nullable :- Unlike the primary key, a unique key allows for at least one NULL value. This means that while NULL values are allowed, any non-NULL values must be unique.
Use Cases :- Unique keys are often used when a column or set of columns needs to have unique values but can also contain NULLs. This is different from primary keys, which are not nullable.
Multiple Unique Keys :- A table can have multiple unique keys. Each unique key enforces uniqueness independently.
Indexed :- Similar to primary keys, unique keys are often automatically indexed by the database management system (DBMS) to improve query performance.
Syntax in Table Creation :- In SQL, you can specify a unique key constraint when creating a table.
For example :-
CREATE TABLE ExampleTable (
ID INT,
UniqueColumn VARCHAR(50) UNIQUE,
AnotherColumn INT,
PRIMARY KEY (ID)
);
Before this example, UniqueColumn has a unique key constraint.
Use in Relationships :- Unique keys can be used in relationships between tables. Columns with unique key constraints in one table can be referenced as foreign keys in another table.

Unique keys are useful when you need to enforce uniqueness in a column but still allow for the possibility of NULL values. They provide more flexibility than primary keys in this regard. However, primary keys are typically used when you want a column or set of columns to uniquely identify each row, and NULL values are not allowed.

Candidate Key

A candidate key is a set of one or more columns (attributes) in a database table that can uniquely identify each record in that table. These keys are considered as potential primary keys for the table. There may be multiple candidate keys in a table, but only one is chosen as the primary key.

Here are some key characteristics and points about Candidate keys:

Uniqueness :- A candidate key must have the property that the values in the columns uniquely identify each row in the table. No two distinct rows should have the same combination of values in the candidate key columns.
Minimality :- A candidate key should be minimal, meaning that no proper subset (a subset that is not equal to the candidate key itself) should have the uniqueness property. Removing any column from the candidate key would result in a loss of uniqueness.
Candidate for Primary Key :- Every candidate key is a potential primary key. However, only one candidate key is chosen to be the primary key for a given table.
Example :- In a table of employees, possible candidate keys could include Employee ID, Social Security Number (SSN), or a combination of attributes like (FirstName, LastName, DateOfBirth).
Multiplicity :- A table can have multiple candidate keys. These keys may be single-column keys or combinations of columns.
Selection of Primary Key :- From the set of candidate keys, one key is chosen to be the primary key for the table. The choice of the primary key depends on factors like stability, simplicity, and ease of use.
Use in Relationships :- Candidate keys are often used in relationships between tables. The columns of a candidate key in one table may be referenced as foreign keys in another table.
Enforcing Uniqueness :- In practice, the database management system (DBMS) enforces uniqueness constraints on candidate keys to ensure data integrity.
Here’s a simplified example in SQL:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
SSN VARCHAR(11) UNIQUE,
FirstName VARCHAR(50),
LastName VARCHAR(50)
);
Before this example, both EmployeeID and SSN could be considered candidate keys, but EmployeeID is chosen as the primary key. The UNIQUE constraint on SSN indicates that it is also a candidate key, providing an alternative unique identifier for each employee.

Example:-

The following table shows, the examples of the keys in DBMS. There are two tables in here. Such as Employee and Designation. Employee Table having 5 attributes. They are EmpID, Emp_Name, EmpLicence, EmpPassport and DId. The Designation Table having 2 attributes such as DId and Designation.

In Employee Table Primary Key is EmpID. Because it is a unique value, it can’t multiple. The designation table Primary Key is DId.

The Employee Table having Foreign. It is from Designation table’s Primary Key. That is DId.

There are some candidate keys here, E.g:- EmpID & EmpLicence & EmpPassport. Both are combine to come to the candidate key.

The EmpLicence & EmpPassport attributes are the Alternate Key. If the attribute can come only the Alternate Key, it can’t combine alternate key.

The EmpLicence and the EmpPassport attributes are the unique Key individually. It same like Primary Key but in a table having only one Primary Key, Multiple Unique Key. In this Key also have attributes for the Primary Key. Such as, It data’s can’t be duplicate and also it can’t be the null values. It can have only one null Value.

Importance points of Keys : —

All the Alternate Keys are Super Key.
All the Primary Keys are Super Key.
Unique Key have only 1 null value.
Primary Key don’t have Null values.
All the Primary Keys are Unique Keys.
All the Unique Keys are not Primary Keys.
All the Composite Keys are Primary Keys.
All the Alternate Keys are Candidate Keys.
Foreign Keys are used to connect the table.

In summary, keys in a DBMS are crucial for maintaining data accuracy, establishing relationships between tables, and ensuring efficient data retrieval. The choice of which key(s) to use depends on the specific requirements of the database schema and the nature of the data being stored. Properly defined keys are essential for the overall effectiveness and reliability of a database system.