Various Types of Key in Relational DBMS
Simple explanation of keys with an example that is frequently asked in interviews and is a part of DBMS beginners tutorial.
So far, I have come across numerous articles that explain the types of Relational Database keys, and sometimes articles contain some key’s name that I haven’t even heard the name. And, I always felt confused while preparing for interviews or when someone asks questions about keys. Thus, I have prepared this article as a source with a simple explanation of each key with an example.
First, I will explain what are keys in DBMS?
KEYS in DBMS is an attribute or set of attributes that help you to identify a row(tuple) in a relation(table). They allow you to find the relation between two tables. Keys help you uniquely identify a row in a table by a combination of one or more columns in that table. Key is also helpful for finding unique records or rows from the table. Database key is also helpful for finding unique records or rows from the table.
Example:
In the above-given example, Employee ID is a primary key because it uniquely identifies an employee record. In this table, no other employee can have the same employee ID.
Why we need Key?
- First, we need to know why we need RDBMS over traditional file system, the answer is simple that we need a database which is consistent and non-redundant. This can be achieved by normalization of the database. Normalization is a technique through which we make our database more consistent by removing the redundancy and data anomalies (deletion and insertion).
Normalization is used for mainly two purposes,
- Eliminating redundant(useless) data.
- Ensuring data dependencies make sense i.e. data is logically stored.
We achieve this normalization by using “keys”.
- Keys help you to identify any row of data in a table. In a real-world application, a table could contain thousands of records.
- Allows you to establish a relationship between and identify the relation between tables
- Help you to enforce identity and integrity in the relationship.
Mainly, there are 8 types of keys in DBMS as per my research and knowledge. They are as follows:
- Primary key
- Candidate Key
- Alternate Key
- Foreign key
- Super Key
- Natural Key
- Surrogate Key
- Compound Key
Primary Key:
- The column with the primary key in a table that uniquely identify every row in that table.
- The Primary Key can’t be a duplicate meaning the same value can’t appear more than once in the table. I.e the column with the primary key does not have duplicate values
- A table cannot have more than one primary key.
For example: In the following example, the ID column has the primary key meaning records of the ID column do not have any duplicate value and it can be used to identify data of the table.
Candidate Key:
- Sometimes in a table, there are a group of columns having unique values. All such columns have a candidate key and thus the primary key is a subset of the candidate key.
- Every table must have at least a single candidate key. A table can have multiple candidate keys but only a single primary key.
- For example: In the following example, the ID, Roll Number, and mail ID column has the candidate key meaning records of these columns do not have any duplicate value and it can be used to identify data of the table. And, any one of the three can be taken as the primary key
Alternate Key:
All columns having a candidate key except the primary key have an alternate key.
For example: In the above example, the columns — Roll Number and Email ID have alternate keys, considering ID has the primary key.
Foreign Key:
A foreign key is for a column that creates a relationship between two tables. The purpose of Foreign keys is to maintain data integrity and allow navigation between two different instances of an entity. It acts as a cross-reference between two tables as it references the primary key of another table.
For example: The Department_ID has a foreign key in the following diagram.
Super Key:
- Super key is a set of an attribute which can uniquely identify a tuple.
- Super key is a superset of a candidate key.
For example: In the above EMPLOYEE table/diagram, 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.
Natural Key:
- A natural key is a key which aims to uniquely identify each record and data is somehow related to real-world applications.
- For example, the aadharCardNum column is customers table has a natural key as it can be uniquely identified as well as has some correlation with the real-world.
Surrogate Key:
A surrogate key is an artificial key which aims to uniquely identify each record. This kind of partial key in DBMS is unique because it is created when you don’t have any natural primary key.
- A surrogate key is a value generated right before the record is inserted into a table.
For example: a retailer manager stores data with unique customerID for data storage, then the manager generates customerID on his/her own way that do not have any relation with other real-world data. Thus, customerID has a surrogate key.
Compound Key:
- Compound key has two or more attributes that allow you to uniquely recognize a specific record. It is possible that each column may not be unique by itself within the database.
- However, when combined with the other column or columns the combination of composite keys become unique. The purpose of the compound key in the database is to uniquely identify each record in the table.
- For example: In the following, OrderNo and ProductID can’t be a primary key as it does not uniquely identify a record. However, a compound key of Order ID and Product ID could be used as it uniquely identified each record.
References:
If you have any suggestions or query, feel free to reach me out: LinkedIn
