7 Types of Keys in DBMS Explained

Summary

In this article, you will learn 7 types of keys in DBMS. Take a glimpse below.

1. Primary Key
2. Super Key
3. Candidate Key
4. Alternate Key
5. Foreign Key
6. Composite Key
7. Unique Key

Read the full article to learn more in detail.

What are the Keys in DBMS?

A key in DBMS is an attribute or a set of attributes that help to uniquely identify a tuple (or row) in a relation (or table). Keys are also used to establish relationships between the different tables and columns of a relational database. Individual values in a key are called key values. Check out our free courses to get an edge over the competition.

This blog will cover everything you need to know about the keys in DBMS and attribute closure to find the Key of any relation (table). Stick till the end of the article for some critical GATE questions on keys in DBMS.

For example, every unique identification number is used to identify candidates in an educational institute. These can also help find all the available details maintained on the server about the candidate, such as their address, passport number, or phone number are keys unique to each candidate. Keys are imperative for analyzing and identifying data types. You can also consider doing our Java Bootcamp course from upGrad.

Why are the Keys Required?

A key is used in the definitions of various kinds of integrity constraints. A table in a database represents a collection of records or events for a particular relation. Now there can be thousands and thousands of such records, some of which may be duplicated.

Featured Program for you: Fullstack Development Bootcamp

There should be a way to identify each record separately and uniquely, i.e. no duplicates. Keys allow us to be free from this hassle.

Let us take a real-life example of the database of each student studying in an engineering college.

What attribute of the student do you think will uniquely identify each of them? You could refer to a student by using their name, department, year and section. Or, you can mention only the university roll number of the student, and you can get all the other details from that. 

A key could either be a combination of more than one attribute (or columns) or just a single attribute. The main motive of this is to give each record a unique identity.

Also Read: DBMS vs RDBMS 

Different Types of Keys in DBMS

There are broadly seven types of keys in DBMS. All these types of keys in SQL must be implemented appropriately for the relevant database to negate redundancy. Correct identification will lead to database accuracy, improving results in a limited time. Let’s explore these DBMS keys to learn more about what are keys in SQL.

1. Primary Key
2. Candidate Key
3. Super Key
4. Foreign Key
5. Composite Key
6. Alternate Key
7. Unique Key

Let’s look at each of them separately.

1. Primary Key

If you’re wondering what is primary key in DBMS,  primary key is a column of a table or a set of columns that helps to identify every record present in that table uniquely. There can be only one primary Key in a table. Also, the primary Key cannot have the same values repeating for any row. Every value of the primary key must be different with no repetitions. Amid many details, a primary key is the most significant one to understand what are keys and what is primary key in DBMS.

The PRIMARY KEY (PK) constraint put on a column or set of columns will not allow them to have any null values or any duplicates. One table can have only one primary key constraint. Any value in the primary key cannot be changed by any foreign keys (explained below) which refer to it.

Examples of Primary Keys 

In this example, the student ID is the primary key.

2. Super Key

Super Key is the set of all the keys which help to identify rows in a table uniquely. This means that all those columns of a table than capable of identifying the other columns of that table uniquely will all be considered super keys.

Super Key is the superset of a candidate key (explained below). The Primary Key of a table is picked from the super key set to be made the table’s identity attribute.

Examples of Super Keys 

Here is a table with examples of super keys in a relational database context. Assume we have a table named Students with the following attributes: StudentID, RollNo, FirstName, LastName, and Email.

upGrad’s Exclusive Software and Tech Webinar for you –

SAAS Business – What is So Different?

3. Candidate Key

Candidate keys are those attributes that uniquely identify rows of a table. The Primary Key of a table is selected from one of the candidate keys. So, candidate keys have the same properties as the primary keys explained above. There can be more than one candidate keys in a table.

There can be more candidate keys than just one for any table, but they can never be empty. Every candidate key carries unique information and value. Besides these characteristics, a combination of attributes also works as a set of candidate keys.

Examples of Candidate Keys

4. Alternate Key

As stated above, a table can have multiple choices for a primary key; however, it can choose only one. So, all the keys which did not become the primary Key are called alternate keys.

Examples of Alternate Keys

In this table:

• The primary key could be StudentID, which uniquely identifies each record.
• The alternate keys are RollNo and Email, each of which could also uniquely identify a record but are not chosen as the primary key.

5. Foreign Key

Foreign Key is used to establish relationships between two tables. A foreign key will require each value in a column or set of columns to match the Primary Key of the referential table. Foreign keys help to maintain data and referential integrity. 

Foreign keys are essential for maintaining a difference between two entities that might be linked with the same information but do not share similar information. In such cases, the tables are linked to maintaining a connection but do not entirely work as a replacement for each other. For instance, any individual working for the marketing department might have marketing department information in its employee table, but that does not mean the table can be similar to the department table held by the marketing department.

Examples of Foreign Keys

Department Table
 

Teacher Table

Composite Key

A composite Key is a set of two or more attributes that help identify each tuple in a table uniquely. The attributes in the set may not be unique when considered separately. However, when taken all together, they will ensure uniqueness. The ‘concatenated key’ is another name for a composite key.

Examples of Composite Key

In this table:

• StudentID alone does not uniquely identify a record because a student can register for multiple courses.
• CourseID alone does not uniquely identify a record because a course can have multiple students registered.
• The combination of StudentID and CourseID forms a composite key that uniquely identifies each record in the CourseRegistrations table.

7. Unique Key

Unique Key is a column or set of columns that uniquely identify each record in a table. All values will have to be unique in this Key. A unique Key differs from a primary key because it can have only one null value, whereas a primary Key cannot have any null values.

Among the seven types of keys available in the DBMS, a few other types of keys in SQL are also accessible. The key type is called Artificial Keys. An artificial has no relevance or meaning to the business but is often used to tackle conflicting data management situations. For example, if there is no attribute that comprises all required primary key properties or if the primary keys are complex.

Employees Table

In this table:

• Each EmployeeID is unique, but it’s not the unique key because it’s specifically generated for each employee and might not be used externally.
• Email is chosen as the unique key because it uniquely identifies each employee. No two employees can have the same email address in this scenario.

A comparison of Primary and Unique key attributes in DBMS

Keys play a significant role when it comes to DBMS Relational Algebra, with key in database . There are multiple categories or types of keys in DBMS with example. They include alternate key, super key, candidate key, foreign key, primary key and unique key in DBMS. Every class of key carries its significance and level of implementation in DBMS including simple key in DBMS.

However, the primary key and unique key in DBMS are the most prevalent and most implemented ones. Here’s How the Unique key differs from the primary key in DBMS or key in relational database.

This table shows the differences between primary and unique keys in a database. It compares them based on how unique they need to be, whether they’re the main identifier for records, how many can exist in a table, how they’re used, and gives examples.

Primary Key in DBMS and Concept of keys in DBMS

The primary  DBMS key is a table infused with a single column that can identify every row individually. This key can enforce constraints of integrity to the particular table. One must also note that just a single primary key is allowed for utilization in a table. The primary key can implemented in the table but does not allow any Null or duplicate values and types of key. The value of the primary key present in the table fluctuates rarely. Therefore, it needs to be selected carefully where the alterations can take place in specific cases.

The attribute of role number is never supposed to have a Null or a similar value. This is because every candidate enrolled in the institution can get individual role numbers. Henceforth, two candidates can never have relevant roll numbers. Every row included in the table is identified individually, along with the roll numbers of candidates. Unlike the case of a unique key in DBMS, one can consider the attribute of roll number as a primary key in this situation in . 

The Most Critical Features in Primary Keys

Before working with the primary key, check out some of the essential components of primary keys:

• There can be no room for duplicate rows when it comes to the case of Primary keys.
• Just one primary key can be used when working with the table.
• The primary key comprises no Null constraints.
• Primary keys can be formed from a single table or multiple fields of tables. 

Unique Key in DBMS

Constraints of the Unique Key in DBMS can determine the rows in entirely unique ways in relevancy or the table. The table can consist of more than a single unique key, different from the primary key. Constraints of Unique Key in DBMS can easily receive a single Null value when working with the column. The unique key constraints can also attain reference from the foreign key of any other table. It can be utilized when a user needs to incorporate the unique key constraints within a group of columns or a single column and does not come with a primary key.

The attributes of roll numbers are assigned with the citizen identification and primary keys or types of keys in DBMS. They can get unique constraints where every entry in the column of citizen ID must be different. This is because every citizen of the nation needs to get their UIN identity similar to an Adhaar number. However, if the candidate has migrated to a different nation, in this scenario, then they would not get any citizen identification. The entry can often get a Null value as just one Null is accepted in the constraint of Unique Key in DBMS.

The Main Properties of Unique Key in DBMS

• When asked, how many keys are there in DBMS, there might exist more than a single Unique Key in DBMS for the table
• The Unique Key in DBMS comes with many significant features. Some of them are demonstrated below.
• The unique keys treasure the liberty of receiving column-based Null values.
• These keys can be created from a single or more number of tables
• The foreign keys can be referred to as a Unique Key in DBMS if there is a need for reference or providing key attribute in DBMS.

Determining the Key Values Are All Unique

The Unique Key in DBMS can be imposed routinely or by default for ISAM, Hash or the B-tree tables by utilizing the process of modifying. Speaking of which, some of the sheer advantages of the unique keys include the following:

• A proper design of a database often offers a Unique Key in DBMS and helps in improving the overall performance.
• One can be easily sure about the addition of all information to the table that includes the Unique Key in DBMS.
• In most scenarios, the unique keys are a significant source of benefit when it comes to business organizations. Therefore, it is often a part of the systems of small, medium, and large-scale ventures belonging to various industries today. 

However, as explained above, the primary key is not going to allow or receive any category of Null values. The table should include a single primary key, while the Unique Key in DBMS will allow the incorporation of Null values. The table can get just a single primary key, while multiple unique ones are on the table. The clustered index can be generated by default when the primary key can be identified, and the unique key creates a non-clustered index. However, it is imperative to keep in mind that the primary key can be considered as a Unique Key in DBMS. However, the unique key cannot be viewed as a primary key.

Working With the DBMS Keys Successfully

f your database management system supports foreign and primary keys, you will be able to work with power builder keys. Learning how to implement and execute the keys in DBMS in the right way is essential. 

It is vital to check that your DBMS system is supportive of the leys that you are supposed to use. Users shall be able to use the foreign and primary keys to incorporate the referential authenticity of the database system. In this way, one can depend on the DBMS to ensure that only valid values have been used. This process is applicable in the case of specific columns rather than having to write the code for enforcing authentic values.  

For instance, if you have two different tables known as Table A and Table B, the latter can comprise the head identification of Table A, which holds the identity of Table B’s manager. You will need to ensure that just the ID of the authentic table B is supposed to be incorporated into the table. The values that are valid for table A are the actual values for table B in the particular table.  

In order to define and incorporate this type of relationship, you will need to identify the foreign key for table A that points towards table B. By placing the key in the proper position, the database system of management lets any value for table B that does not match with the value of table A. In order to get a more in-depth understanding of this process, one can look for cutting-edge DBMS programs. The suitable courses infused with pragmatic knowledge can also help you design your own database or create industry-centric DBMS documentation. 

Functional Dependencies

Now that we know a different kind of keys in DBMS, let’s see how to identify them when given a table from a database. For this, we use the concept of functional dependencies.

A functional dependency (FD) is a constraint between two sets of attributes. This constraint is for any two tuples t1 and t2 in r if t1[X] = t2[X], then they have t1[Y] = t2[Y]. This means the value of the X component of a tuple uniquely determines the value of component Y. 

FD is denoted as X ? Y (this is read as “Y is functionally dependent on X”). The left side is called the determinant, and the right side is called the dependent.

Closure of a set of Attributes

A closure is a set of all possible FDs derived from a given set of FDs. It is also referred to as a complete set of FDs. If F is used to donate the set of FDs for relation R, then the closure of a set of FDs implied by F is denoted by F+.

We will now define the closure of a set of attributes concerning a given set of FDs. It will help identify the super Key of the relationship and find whether an FD can be inferred from a given set of FDs or an FD is redundant. After finding a set of functional dependencies on a relation, the next step is to find the Super Key for that relation (table).

Then we find out the set of attributes’ closure to decide whether an attribute (or set of attributes) of any table is a key for that table or not. The set of attributes that are functionally dependent on the attribute X is called Attribute Closure of X, and it can be represented as X+.

Below are some rules needed to determine F+:

1. Reflexivity: If X is a superset of Y or Y is a subset of X, then X ? Y.
2. Augmentation: If X ? Y, then XZ ? YZ. Or If Z ⊆W, and X ? Y, then XW ? YZ.
3. Transitivity: If X ? Y and Y ? Z, then X ? Z.
4. Union: If X ? Y and X ? Z, then X ? YZ.
5. Decomposition: If X ? YZ, then X ? Y and X ? Z.
6. Pseudo-Transitivity: If X ? Y and YW ? Z, then XW ? Z.

How to find Candidate Keys and Super Keys using Attribute Closure?

• If the attribute closure of an attribute set contains all attributes of relation, the attribute set will be super Key of the relation.
• If no subset of this attribute set can functionally determine all the relation attributes, that set will be the candidate key.

Also visit upGrad’s Degree Counselling page for all undergraduate and postgraduate programs.

Let’s discuss a few previously asked GATE questions to see the applications of attribute closure.

GATE 2014

Consider the relation scheme R = {E, F, G, H, I, J, K, L, M, N} and the set of functional dependencies {{E, F} ? {G}, {F} ? {I, J}, {E, H} ? {K, L}, K ? {M}, L ? {N} on R. What is the key for R?

(A) {E, F}

(B) {E, F, H}

(C) {E, F, H, K, L}

(D) {E}

Approach: We will check the attribute closure of all the options provided. The set whose closure will give us the entire relation R will be the correct answer.

A: {E, F} + = {EFGIJ} ≠ R

B: {E, F, H} + = {EFGHIJKLMN} = R 

C: {E, F, H, K, L} + = {EFGHIJKLMN} = R

D: {E} + = {E} ≠ R

Both options B and C give us the entire relation scheme. However, we choose the minimal option to be the correct answer because a Candidate Key should be the minimal Super Key.

Answer: B

GATE 2013

Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values. F = {CH ? G, A ? BC, B ? CFH, E ? A, F ? EG} is a set of functional dependencies (FDs) so that F+ is exactly the set of FDs that hold for R.

How many candidate keys does the relation R have?

(A) 3

(B) 4

(C) 5

(D) 6

Approach: We will take the LHS of each functional dependency given in the question and find their attribute closures.

CH+ = G

A+ = ABCEFGH

B+ = ABCEFGH

E+ = ABCEFGH

F+ = ABCEFGH

So we see that closures of A, B, E, F have the entire relationship except for attribute D. So there is a total of 4 candidate keys AD, BD, ED and FD.

Answer: B

Learn Software Courses online from the World’s top Universities. Earn Executive PG Programs, Advanced Certificate Programs, or Masters Programs to fast-track your career.

The Perks of Working with Functional DBMS Keys

Enhanced Data Security and Information Sharing

By offering rapid solutions to the queries of the database, the system of database management can encourage many quick and easy results. Not only that, in a short span of time, it can also provide more accurate information. The end users, such as the salesperson, can speed up the patterns and cycles of sales and attain more perfect results when it comes to sales prospecting. Therefore, a robust system of database management is a must when it comes to prospecting sales. 

Robust Information Integration System

Utilizing the system of database management can easily demonstrate how the procedure in a single facet of a firm can influence the other teams. An active and functional design of database management can effortlessly integrate the options for normalizing the information across several sources. This method is also applicable when it comes to getting rid of segments and duplicates. It also helps in enriching the sets of data that are in custom workflow. 

Providing Reliable and Consistent Data

Inconsistent information takes place when the various versions of the matching information exist in various spaces. For instance, if one team has a customer’s correct contact information and another team have the incorrect contact number. By utilizing the right and functional database system along with information quality checklists, one can ensure that the information is viewed and cleansed perfectly across your venture. 

Helps Boost Productivity

A cutting-edge system of database management can empower individuals to spend a tremendous amount of time on strategic and high-value tasks. It can significantly reduce the need and time for manual scrubbing notes.
 

Comparison of 7 Types of keys in DBMS

Conclusion

Keys and functional dependencies play a very vital role in designing a database. These concepts also help to find the difference between good and bad database design. The final process of removing redundancies and making the database efficient is normalization, which uses all concepts mentioned in this article.

Keys’ characteristics of establishing integrity and identifying the relationship between two tables are their most vital feature, allowing users to edit data with precision while maintaining uniqueness. From alternate keys to composite, the goal is to separate the identity of each record through unique keys defining separate qualities.

If you’re interested to learn more about full-stack development, check out upGrad & IIIT-B’s Executive PG Program in Full-stack Software Development, which is designed for working professionals and offers 500+ hours of rigorous training, 9+ projects, and assignments, IIIT-B Alumni status, practical hands-on capstone projects & job assistance with top firms.

Explore our popular tutorials on various technologies, including JavaScript, SQL,DBMS, Data Structure, JQuery, HTML, Cyber Security, C++, Deep Learning, and Agile Scrum.