Home »
RDBMS
Keys and their types in RDBMS
Here, we are going to learn about the various Keys and their types in RDBMS, with examples.
Submitted by IncludeHelp, on November 10, 2020
Key
A key is an attribute of a database table that is used to access database records. A key may be a single attribute or a group of attributes, which can serve as a key for a combination. A key in DBMS permits us to find the relation between two or more tables. The key is also useful for finding the table’s specific record or lines. The database key is also useful for the table to locate specific records or rows.
Example:
| Student ID |
S.Name |
Surname |
| 101 |
Aman |
Sharma |
| 201 |
Suresh |
Jha |
| 301 |
Geeta |
Nair |
In the above-given example, Student ID is a primary key because it identifies a student record uniquely. In this table, no other student can have the same student ID.
Need of Key in RDBMS
Keys are an important part of the model of the Relational Database. They are used to build and define relationships between tables and also to identify any record uniquely.
The necessities of keys in DBMS are as follows-
- Keys help us to identify any row of data in a table.
- Keys ensure to uniquely identify a table record.
- Keys allow establishing a relationship between tables.
- Keys to enforce identity and integrity in the relationship.
Types of Keys in Database Management System
In DBMS, there are primarily seven different types of keys and each key has a different feature:
1) Super Key
A super key is a collection of multiple keys representing a table's rows. There may be additional attributes to a Super key that are not needed for specific identification.
In the above-given example, Student ID, SRollNo, S.Name, Surname, Email are superkeys.
2) Primary Key
The primary key is a column or column group in a table that defines each row in that table uniquely. It is unlikely for a table to have more than one primary key.
In the above-given example, Student ID is a primary key.
3) Candidate Key
A Candidate key is a set of attributes that classify tuples in a table uniquely. The candidate key is a set of keys that uniquely identify tuples into a table.
The Candidate key is a super-key without repeated attributes. This means that we can pick the primary key from the candidate key. Each table must have a key for at least one candidate. A table can have several key candidate key but only a single primary key. Candidate key must not contain null values
In the given table Student ID, SRollNo, Email are candidate keys which help us to uniquely identify the student record in the table.
4) Alternate Key
The Alternate key is a category of a column in a table that uniquely identifies each row in that table. For a primary key, a table may have several options, but only one can be set as the primary key. An Alternative key is named for all keys that are not primary keys.
In this table, Student ID, SRollNo, Email is qualified to become a primary key. But since Student ID is the primary key so SRollNo, Email becomes the alternative key.
5) Foreign Key
A foreign key is a column that sets up a connection between two tables. The aim of foreign keys is to maintain the integrity of the data and to allow two separate instances of the entity to be navigated.
| CourseID | Coursename |
| 1 | B.Tech |
| 2 | MCA |
| 5 | M.Tech |
After setting up a foreign key, the new one table is –
The primary key of table is always become a foreign key of another table. After setting a foreign key, a relation can be established between two tables, and then we can access the records from these tables.
6) Compound Key
A compound key has two or more attributes that allow the unique identification of a specific record. Inside the database, it is possible that each column may not be unique per se.
7) Composite Key
A composite key, an artificial key that attempts to uniquely identify each record, is called a surrogate key. Such key types are unique because they are created when you don't have any natural primary key.
8) Surrogate Key
A surrogate key is referred to as a surrogate key, an artificial key which seeks to uniquely recognize each record. Such key types are unique because they are created when you don't have any natural primary key.