If you are a beginner or intermediate then this article gives you a good overview of the key constraints in DBMS and if you are an expert then it may help you to get something new on the same or help to refresh your knowledge. Well, in this article post, you will learn all about different types of key constraints in DBMS along with their importance to maintain the integrity and accuracy of the data.
Introduction to Key Constraints in DBMS
Guys, Key constraints are an attribute or set of attributes that are used to uniquely identify a row in a table. Keys are used to restrict the type of data that can be entered into a database table. Keys help to ensure the accuracy and integrity of the data in the database. There are several different types of key constraints in DBMS, including primary key, foreign key, and unique key constraints. Refer to the below example –
Example:
| Vendor_ID | Vendor_Name | Vendor_Location |
| 11 | Johnson Brothers | Goregaon, Mumbai |
| 12 | Vivek and Roberts | Nariman Bihar New Delhi |
| 13 | Desuza and Company | Vijaynagar Bangalore |
In the above-given example, vendor_id is a primary key because it uniquely identifies vendor data. In this table, no other vendor can have the same vendor_id.
Importance of Key Constraints in DBMS
A DBMS (Database Management System) is software that interacts with users, external applications, and other databases to manipulate and retrieve data to analyze or in various aspects. Constraints are one of the key features of a DBMS that ensures the integrity and accuracy of data in the database. There are basically nine (9) key constraints in the database including entity integrity, domain integrity, referential integrity, and user-defined integrity. Key constraints help to ensure the accuracy and consistency of data in a table within the database. Keys are implemented on a column or a group of columns to ensure the accuracy and integrity of the data in the database. Maintaining the integrity and accuracy of the data at the column level in a table of the database itself is the importance of key constraints in DBMS.
Type of Key Constraints in DBMS
There are mainly 9 types of key constraints in DBMS as given below –
- Primary Key
- Unique Key
- Foreign Key
- Super Key
- Candidate Key
- Alternate Key
- Compound Key
- Composite Key
- Surrogate Key
1. Understanding Primary Key Constraints in DBMS
Primary key is the unique identifier for each record in a table that is defined on a column or on a group of columns. It is used to identify and locate a specific record. If a foreign key refers to a primary key then the value can not be updated or modified defined in a primary key column of a table. The value can not be null for a primary key column.
Example: In the following ‘Vendor’ table, Vendor_ID is a primary key, Two vendors can not have the same vendor_id and every vendor must have a unique id. Primary key constraints are important because they ensure that each record in a table is unique and can be identified.
| Vendor_ID | Vendor_Name | Vendor_Location |
| 11 | Johnson Brothers | Goregaon, Mumbai |
| 12 | Vivek and Roberts | Nariman Bihar New Delhi |
| 13 | Desuza and Company | Vijaynagar Bangalore |
2. Understanding Unique Key Constraints in DBMS
A unique key constraint is a unique identifier for each record in a table that is defined on a column or combination of columns. Unique key guarantees the uniqueness of the values within that column or a combination of columns. Unique Key allows only one NULL Value for unique key columns.
Example: In the following ‘Vendor’ table Vendor_ID is a Unique key, A unique constraint could be applied to the combination of the vendor_name and vendor_id. Two vendors can not have the same vendor_id and vendor_name, and every vendor must have a unique combination of vendor_id and vendor_name. Unique key constraints are important because they ensure that each record in a table is unique and can be easily identified.
| Vendor_ID | Vendor_Name |
| 11 | Johnson Brothers |
| 12 | Vivek and Roberts |
| 13 | Desuza and Company |
3. Understanding Foreign Key Constraints in DBMS
A foreign key constraint is a rule that is used to ensure the accuracy and uniqueness of data in a data table. A foreign key is defined as a column or set of columns in one table that references the primary key of another table. The purpose of a foreign key constraint is to ensure that the data in the table meets the referential integrity rules for the database.
A foreign key links two tables together and it refers to the primary or unique key in another table. The table that holds the foreign key is called a child table and that holds the primary key or unique key is called the parent table. Create a foreign key constraint as follows –
Example: We have taken two different tables to implement the foreign key, 1st table is Department where ‘Department_ID’ is the primary key and 2nd table are Employee where ‘Employee_ID’ is the primary key and the foreign key is implemented on ‘Department_ID’ column and it refers to 1st table’s ‘Department_ID’ column, that means the particular employee belongs to which department.
Department Table
| Department_ID | Department_Name |
| 11 | IT |
| 12 | Business |
| 13 | HR |
Employee Table
| Employee_ID | Employee_Name | Office_Location | Department_ID |
| 2 | Vivek | Goregaon, Mumbai | 13 |
| 3 | Roberts | Nariman Bihar New Delhi | 11 |
| 4 | Desuza | Vijaynagar Bangalore | 12 |
The addition of a foreign key creates a relation between two different tables like Department and Employee tables and this concept is known as referential integrity.
4. Understanding Super Key
A super key is a set of one or more than one key that uniquely identifies a data row in a table and the given relation. There is no limit to specific numbers of keys in a super key. A super key can have other attributes or keys also that are not necessary for unique identification.
Example: In the following table Employee_ID and Department_ID both are super keys –
| Employee_ID | Employee_Name | Office_Location | Department_ID |
| 2 | Vivek | Goregaon, Mumbai | 13 |
| 3 | Roberts | Nariman Bihar New Delhi | 11 |
| 4 | Desuza | Vijaynagar Bangalore | 12 |
5. Understanding Candidate Key
A candidate key is a set of one or more than one key column in a table that can be used to uniquely identify each row in a table. The candidate key is also a super key which has no repeated key columns. Each table must have one candidate key and also a table can have more than one candidate key. A table can have only one primary key in a database. The candidate key should have minimum key columns that ensure unique data in a table.
Example: In the following table Employee_ID, Mobile and Desk_No are the candidate keys –
| Employee_ID | Employee_Name | Office_Location | Mobile | Desk_No |
| 2 | Vivek | Goregaon, Mumbai | 9876543210 | 201 |
| 3 | Roberts | Nariman Bihar New Delhi | 9876543210 | 202 |
| 4 | Desuza | Vijaynagar Bangalore | 9876543210 | 203 |
6. Understanding Alternate Key
The alternate key is a set of one or more than one key columns in a table that can be used to uniquely identify each row in the table. A table can have one or more than one primary key where only one primary key can be implemented as the primary key and the rest will be unused which are called alternate keys.
Example: In the following table Employee_ID, Mobile and Desk_No are primary keys but as the Employee_ID is the primary key then Mobile and Desk_No will be alternate keys –
| Employee_ID | Employee_Name | Office_Location | Mobile | Desk_No |
| 2 | Vivek | Goregaon, Mumbai | 9876543210 | 201 |
| 3 | Roberts | Nariman Bihar New Delhi | 9876543210 | 202 |
| 4 | Desuza | Vijaynagar Bangalore | 9876543210 | 203 |
7. Understanding Compound Key
The compound key is a set of two or more key columns in a table that can be used to uniquely identify each row of data in a table. It is possible that each key column may not be unique by itself but with the combination of two or more key column keys, it becomes unique. Any part of the compound key may be a foreign key.
Example: In the following table Employee_ID, Email, Mobile, and Department_ID can not be a primary key as they will not uniquely identify a data row however a compound key of Employee_ID, Email, Mobile, and Department_ID can be used and it will uniquely identify each row data, where Department_ID is a foreign key.
| Employee_ID | Employee_Name | Mobile | Department_Id | |
| 2 | Vivek | vivek@gmail.com | 9876543210 | 13 |
| 3 | Roberts | roberts@gmail.com | 9876543210 | 11 |
| 4 | Desuza | desuza@gmail.com | 9876543210 | 12 |
8. Understanding Composite Key
The composite key is a set of two or more key columns in a table that can be used to uniquely identify each row of data in a table. It is possible that each key column may not be unique by itself but with the combination of two or more key column keys, it becomes unique. Any part of the composite key may or may not be a part of the foreign key.
Example: In the following table Employee_ID, Email and Mobile can not be a primary key as it will not uniquely identify a data row however a compound key of Employee_ID, Email, and Mobile can be used and it will uniquely identify each row data.
| Employee_ID | Employee_Name | Mobile | |
| 2 | Vivek | vivek@gmail.com | 9876543210 |
| 3 | Roberts | roberts@gmail.com | 9876543210 |
| 4 | Desuza | desuza@gmail.com | 9876543210 |
9. Understanding Surrogate Key
A surrogate key is an artificial key that is assigned to a record in a database table to uniquely identify the row data in a table, and is not based on any natural primary key. A surrogate key column holds usually an integer and the value is generated before any record is inserted into the table.
Example: In the following table, the Date of Joining is a column that holds a timestamp that inserts at the time any employee joins, and it is uniquely identified for each employee. In this example, the Date of Joining is a surrogate key.
| Employee_ID | Employee_Name | Mobile | Date of Joining | |
| 2 | Vivek | vivek@gmail.com | 9876543210 | 2023-01-22 09:30:12 |
| 3 | Roberts | roberts@gmail.com | 9876543210 | 2023-01-22 09:50:15 |
| 4 | Desuza | desuza@gmail.com | 9876543210 | 2023-01-22 10:00:35 |
FAQs
Keys in DBMS are a rule on one or more than one column that uniquely identifies a row of data in a table.
Primary, Unique, Foreign, Super, Candidate, Alternate, Compound, Composite, and Surrogate Keys are the nine keys in DBMS.
A set of one or more than one column within a table which uniquely identifies a row of data is called a primary key.
A set of one or more than one key columns which are not primary key columns called an alternate key.
A super key that has not repeated key columns is called a candidate key.
A key that is applied to multiple columns to identify rows of data is called a composite key.
A surrogate key is an artificial key that is applied to a record in a database table to uniquely identify the row data in a table, and is not based on any natural primary key is called a surrogate key.
There are basically 4 types of constraints in DBMS –
Entity Integrity, Domain Integrity, Referential Integrity, User-defined Integrity
Conclusion
This tutorial post described the basic knowledge of Key Constraints in DBMS. Key constraints are an important feature of a DBMS. Keys ensure the integrity and accuracy of the data in the database. Primary Key, Unique Key, and Foreign Key are the types of key constraints that can be imposed on a database. Each type of key constraint serves a specific and different purpose. It will provide enough understanding for beginners and professionals.
We hope you would have liked this tutorial very well. Please share this on your social media network so that it can be available to even more needy persons which may be beneficial for them. If you have any questions, feedback, or suggestions regarding this tutorial, please contact us. Do comment right in the comments section below. We will consider your valuable input and try to give you a response asap.
