Keys in DBMS

← prevnext →
21 Apr 2025 | 8 min read

Introduction:
A key feature of the relational database concept is that there should be no duplication of tuples in a
relation so that each tuple can be uniquely identified in the relation. This duplication of tuples can
be achieved by simply listing all their attribute values, but now the question arises whether we need
all the attributes to make a distinction. If we use all the attributes then a lot of time is wasted in
referencing these attributes which will uniquely identify a particular tuple.

The minimal set of attributes that uniquely help identify a tuple of a relation and which cannot be
further reduced are called key attributes or simply key.

What do you mean by Key?

Keys play an important role in the relational database.
It is used to uniquely identify any record or row of data from the table. It is also used to
establish and identify relationships between tables.
A key in a relation that satisfies the following two properties among the means of
indicating uniqueness:

1. Unique Identification: No duplication of tuples.

2. Irreducable: The attributes which form the key cannot be further divided.

For example, ID is used as a key in the Student table because it is unique for each student. In the
PERSON table, passport_number, license_number, SSN are keys since they are unique for each
person.
In DBMS, why are keys necessary?
In order to define different kinds of integrity constraints in a database, we employ keys. In contrast,
a table is a collection of records for different occurrences for any relation. These documents might
number in the thousands, and some of them might even be duplicates.

Therefore, we want a method that allows one to identify each of these entries independently and
uniquely-that is, without creating duplicates. Keys assist to eliminate this inconvenience.

Types of keys:

1. Primary key
It is the first key used to identify one and only one instance of an entity uniquely. An
entity can contain multiple keys, as we saw in the PERSON table. The key which is
most suitable from those lists becomes a primary key.
In the EMPLOYEE table, ID can be the primary key since it is unique for each
employee. In the EMPLOYEE table, we can even select License_Number and
Passport_Number as primary keys since they are also unique.
For each entity, the primary key selection is based on requirements and developers.
A Primary Key's Objective:

Data uniqueness is enforced by the primary key, which stops duplicate records from
being inserted. Retaining data accuracy depends on its distinctiveness.
It offers a rapid and effective means of gaining permission to access particular records
from the table. The main key allows the database to locate a specific row without having
to search the entire table.

2. Candidate key
A candidate key is an attribute or set of attributes that can uniquely identify a tuple.
Except for the primary key, the remaining attributes are considered a candidate key. The
candidate keys are as strong as the primary key.
For an attribute to be a candidate key, the following properties must be satisfied.

1. Uniqueness: No two different tuples of the relation will have the same
entries in all attributes of the key.
2. Irreducibility: No proper subset of the key has uniqueness property. This
means that if the candidate key is made up of more than one attribute then no
individual attribute of the candidate key that participates into it results in a
unique value.

For example: In the EMPLOYEE table, id is best suited for the primary key. The rest of the
attributes, like SSN, Passport_Number, License_Number, etc., are considered a candidate key.
A Candidate Key's Objective:

A candidate key's main goal is to make sure that no two items in a table have the same
set of attribute values combined. It offers a dependable way to identify records
individually.
Choosing primary keys, creating database schemas, and maintaining data integrity all
depend on candidate keys.

3. Super Key
Super key is an attribute set that can uniquely identify a tuple. A super key is a superset of a
candidate key.

For example: In the above EMPLOYEE table, for(EMPLOEE_ID, EMPLOYEE_NAME), the

name of two employees can be the same, but their EMPLYEE_ID can't be the same. Hence, this
combination can also be a key.

The super key would be EMPLOYEE-ID (EMPLOYEE_ID, EMPLOYEE-NAME), etc.

The Super Key's Objective:

Making ensuring that no two records in a database have the same mixture of value of
attributes is the main function of a super key. It supports the preservation of data
 integrity.
Candidate keys, or minimal super keys, are identified using super keys. The
characteristics or sets of characteristics that are utilized as primary keys in the table are
known as candidate keys.

4. Foreign key
Foreign keys are the column of the table used to point to the primary key of another
table.
An attribute or a set of attributes that acts as a foreign key must satisfy the following
two conditions:

1. The domain of the attributes of a foreign key is the same as that of the set of
attributes defined for another relation or the primary key of the same relation.
2. The foreign key data values in any row of the relation must be either null or
match the primary key data values of a tuple of the related relation.

The relation in which the foreign key occurs is the child relation and the relation in
which the referenced attribute occurs is the parent relation.

For Example: Consider the Employee relation and Department relation.

Every employee works in a specific department in a company, and employee and

department are two different entities. So we can't store the department's information in
the employee table. That's why we link these two tables through the primary key of one
table.
We add the primary key of the DEPARTMENT table, Department_Id, as a new attribute
in the EMPLOYEE table.
In the EMPLOYEE table, Department_Id is the foreign key, and both the tables are
related.

A Foreign Key's Objective:

Referential integrity refers to the validity and consistency of relationships across tables,
and it is the essential goal of a foreign key. It stops acts that might jeopardize these
partnerships' integrity.
It guarantees the consistency of data across linked tables. A foreign key may make sure
that orders are linked to legitimate customer IDs, for instance, if you have two tables:
 "Orders" and "Customers".

5. Alternate key
There may be one or more attributes or a combination of attributes that uniquely identify each tuple
in a relation. These attributes or combinations of the attributes are called the candidate keys. One
key is chosen as the primary key from these candidate keys, and the remaining candidate key, if it
exists, is termed the alternate key. In other words, the total number of the alternate keys is the total
number of candidate keys minus the primary key. The alternate key may or may not exist. If there is
only one candidate key in a relation, it does not have an alternate key.

For example, employee relation has two attributes, Employee_Id and PAN_No, that act as
candidate keys. In this relation, Employee_Id is chosen as the primary key, so the other candidate
key, PAN_No, acts as the Alternate key.

The objective of alternate key is:

An alternative key's main goal is to make sure that no two entries in a table have the
same set of attribute values combined. It provides a different method of record
identification from the primary key.
By providing a variety of possibilities for uniquely identifying entries, alternate keys
facilitate flexibility in database architecture. Different qualities may be effectively
employed to identify records based on certain use cases.

6. Composite key
Whenever a primary key consists of more than one attribute, it is known as a composite key. This
key is also known as Concatenated Key.
For example, In employee relation, we assume that an employee may be assigned multiple roles,
and an employee may work on multiple projects simultaneously. So the primary key will be
composed of all three attributes, namely Emp_ID, Emp_role, and Proj_ID in combination. So these
attributes act as a composite key since the primary key comprises more than one attribute.

The objective of alternate key is:

An alternative key's main goal is to make sure that no two entries in a table have the
same set of attribute values combined. It provides a different method of record
identification from the primary key.
By providing a variety of possibilities for uniquely identifying entries, alternate keys
facilitate flexibility in database architecture. Different qualities may be effectively
employed to identify records based on certain use cases.

7. Artificial key
The key created using arbitrarily assigned data are known as artificial keys. These keys are created
when a primary key is large and complex and has no relationship with many other relations. The
data values of the artificial keys are usually numbered in a serial order.

For example, the primary key, which is composed of Emp_ID, Emp_role, and Proj_ID, is large in
employee relations. So it would be better to add a new virtual attribute to identify each tuple in the
relation uniquely.

MCQs on DBMS Keys:

1. In what scenario is a subset of a super key a candidate key?

1. Subset is a super key

2. All subsets are super keys
3. No proper subset is a super key
4. Each subset is a super key

Answer: C

Explanation: Two sets cannot be the same subset of one another. A set from a super key that isn't
the entire super set is called a candidate key.
2. An example of primary key is-

1. last_name
2. address
3. first_name
4. emp_id

Answer: d

Explanation: emp_id is unique, so it is a primary key.

3. Which is not a primary key-

1. roll_no
2. address
3. reg_no
4. emp_id

Answer: b

Explanation: address is not unique, so it is not a primary key.

4. Which key is the primary key of another table?

1. foreign key
2. super key
3. candidate key
4. none

Answer: a

Explanation: Foreign key must be the primary key of another key.

5. Which of the items that describes a collection of one or more characteristics used together
for identifying a record distinctively?

1. Super key
2. Sub key
3. Candidate key
4. Foreign key

Answer: a

Explanation: The superset of every key in a relation is known as the super key.
Next TopicDBMS Generalization