In this tutorial, we will discuss what are DBMS keys? why we need a key in DBMS? and also understand the different types of database keys, so let’s begin
Table of Contents
DBMS Keys:
A DBMS key is an attribute or a set of attributes, which is used to find a database record (row) in a database table.
Why do we need a key in DBMS?
A DBMS key is very important for database tables because it’s the only way that helps to identify a particular row (tuple) or group of rows in the database. It also allows establishing the relationship between tables. When we create a database table, then we also create a DBMS key. A key can be created by a combination of one or more columns in the database table.
Actually, in Database, we store data in the form of tables (called Relation), and in these tables, we store data row-wise (Entity wise) so to find a particular data (Row/Entity), we need something so we can easily find that data. So we choose attributes (can be one or more attributes), which helps to identify any record in the database table.
Types of DBMS Keys
There are various types of keys in the DBMS.
- Super Key
- Candidate key
- Primary Key
- Foreign Key
Super Key:
Super key is a key of one attribute or more than one attribute, which can be used to uniquely identify a row in a table.
Super key is a superset of candidate key means super key contains candidate key attributes and also primary key attribute.
Actually super key is a supreme key, which may contain additional attributes that are not needed for unique identification.
So in the super key, we can contain any attribute but one attribute data value should be different from the other.
For Example, In STUDENTS table have attributes {Roll_no, name, Phone_no, Course, Email, Address}
So in this database table, super key can be:
- {Roll_no} – this attribute all data value is always unique
- {Phone_no} – every person’s phone_no is always different
- {Email} – every person’s email is also always different
- {Roll_no + Name} – name could be same but roll no is always unique
- {Roll_no + Name + Phone_no + Email}
- {Phone_no + Course + Address}
- {Email + Address}
- etc………
So in the above example, anyone can be Super key, means it can contain any attribute but one attribute should have a key attribute (means one attribute should be unique).
Candidate Key:
Candidate key is a key of one attribute or more than one attribute, which can be used to uniquely identify a row in a table.
So it’s the same definition as super key but it’s not the same as the super key.
A candidate key is minimal super key means it’s a super key with no extra attributes.
Candidate key is a subset of super key means this key is selected from a set of super keys. It consists of minimal possible attributes, which uniquely identify each record within a table.
Some points of candidate key:
- We can choose only unique attributes as candidate key, means only those attributes which data value is different from each other
- A candidate key attribute’s data value can never be NULL or empty.
- A candidate key can be one attribute or a combination of more than one attribute.
- In a table, there can be many candidate keys.
- All the candidate keys are also super keys because super key helps to find candidate keys.
In the above database table candidate key can be:
- Roll_No
- Ph_no
Roll_no + Name is not a candidate key.
Roll_no + Ph_no is also not a candidate key, because Roll_no is sufficient to find record. because we need minimal attributes which are sufficient to find record. So only Roll_no is candidate key and only Ph_no is candidate key, but both combination Roll_no + Ph_no is not candidate key
Primary Key:
Primary key is the main key and most important key in the database, which is used to uniquely identify each record in a table.
In database, the database administrator choose a main key (means primary key) that is alone sufficient to find the record in a table
Primary key is selected from a candidate key, means there can be many candidate keys in the table (like Roll_no, Ph_no, Email, etc) but only one candidate key is selected as a primary key.
In this table, we have Roll_no, Ph_no, Email which data values are unique. So DBA can choose any attribute of these as a primary key. But DBA should choose Roll_no as the primary key because every student should have Roll_no if not then he/she is not a student from that school/college.
Some points of Primary key:
- We can choose only unique attributes as primary key, means only those attributes which do not have duplicate values.
- A primary key attribute’s data value can never be NULL or empty.
- In a table, there can be only one primary key.
- A primary key can be one attribute or a combination of more than one attribute. Primary key is not necessarily to be a single attribute because sometimes we don’t have any sufficient single attribute which uniquely identifies each record so in that case, DBA can choose more than one attribute as primary key
When can we choose more than one attribute as the primary key?
DBA can use more than one attribute as a primary key when that database table doesn’t have any single attribute to create a primary key.
For example of the ORDERS table:
This orders table keeps the daily record of purchase orders. So how do we choose the primary key in this table?
We can’t use Customer_ID alone as the primary key because this is an orders table so a customer can order many times so Customer_ID attribute can never unique.
We also can’t use Product_ID alone as a primary key because a product can be ordered many times by customers.
So in this case, we can use both Customer_ID + Product_ID as the primary key because both attributes together can identify the rows uniquely in the table.
Foreign Key:
When we use primary key or candidate key attribute in another table, then that attribute is declared as foreign key.
Actually, foreign key acts as a cross-reference between two tables means it gives reference to the primary key (or candidate key) of another table.
Foreign key is not used to identify the record, it’s just used to points the key attributes of another table.
Some points of Primary key:
- Foreign keys can contain duplicate values.
- It can also contain NULL value (means can be empty).
- This key acts as a cross-reference between tables.
For Example:
In the Orders table, Customer_ID and Product_ID both are foreign keys because these are key attributes of another table. So these attributes act as a cross-reference between two tables.
Also Read: