Home »
DBMS
Keys and Their Types in DBMS
DBMS Keys: In this tutorial, we will learn about the DBMS Keys and their types with the help of examples.
By Shamikh Faraz Last updated : May 28, 2023
What are Keys in DBMS?
In the database management system, a key is an attribute or a set of attributes that is used for uniquely identification of the records i.e., to uniquely identify a tuple (or row) in a relation (or table).
Types of DBMS Keys
There are a total of 11 keys in the DBMS that you can use based on your requirements. DBMS keys are super key, candidate key, primary key, composite key, compound key, secondary or alternative key, non-key attribute, non-prime attribute, foreign key, simple key, and artificial key. Let's discuss them in detail.
1) Super keys
Super key is a set of one or more than one columns (attributes) which uniquely identifies each record in a table. Super key is a super set of candidate key.
For example: Roll No. is unique in relation. This can be selected as a super key. Also we can select more than one column as a super key to uniquely identify a row, like roll no., First name.
2) Candidate keys
Candidate key is a set of one or more than one columns (attributes) which uniquely identifies each record in a table, but there must not be redundant values (repetition of cells) in selected attribute. Candidate key is a sub set of Super key.
For example: Roll No. is unique in relation. This can be selected as a candidate key. Also we can select more than one column as a candidate key to uniquely identify a record. Unlike the super key in above example we can select only those attributes which don't have repeating cells like course code.
3) Primary keys
Primary key is used to uniquely identify a record in relation. The primary keys are compulsory in every table. The primary keys are having model stability, occurrence of minimum fields, being definitive and feature of accessibility.
Only Roll No. is unique in the above table, so it is selected as primary key. Course code can also be selected as a primary key.
4) Composite keys
Composite Key has at-least two or more than two attributes which specially identifies the occurrence of an entity.
In the above example the Roll No. and Course Code is combined to uniquely identify the record in relation.
5) Compound key
Like other keys Compound key is also used to uniquely recognize a record in relation. This can be an attribute or a set of attributes, but the attributes in relation cannot be use as independent keys. If we use them individually, we will not get any unique record.
6) Secondary or Alternative key
The key other than primary keys are called as secondary or alternative keys.
Example: If we consider Roll No. and Course code as primary key then First Name of Student and First Name of Student will be Secondary/alternate keys.
7) Non-key Attribute
The attributes excluding the candidate keys are called as non-key attributes.
Example: If we consider Roll No. and Course code as candidate key then First Name of Student and First Name of Student will be Non Key attribute.
8) Non-prime Attribute
Excluding primary attributes in a table are non-prime attributes.
Example: It is considered as only Roll No. is primary key, so all the remaining attributes will be non prime attributes, but if we considering course code also a primary key than it will not non prime attribute.
9) Foreign keys
Foreign key is a key of one table, which points to the primary key in second table. It has a relationship with primary key in another table.
The "BusinessEntityID" attribute in the "Person" relation is the PRIMARY KEY. The "BusinessEntityID" attribute in the "PersonPhone" relation is a FOREIGN KEY.
10) Simple key
Simple key is a single cell to specially identify a record. The single cell cannot be divided into more cells. Primary key is a super set of simple key.
Example: In the below example student id is a single field because no other student will have same Id. Therefore, it is a simple key.
11) Artificial key
When primary key is very large and complex, then 'Artificial keys' are used. They are allowed when no property has the parameter of primary key. They are generally not used in professional organization.