The word constraint means a limitation to something or someone. So, constraints in DBMS are also some kind of limitation to something. In this article, we will discuss constraints in DBMS, and types of constraints in DBMS. Constraints in DBMS is one of the most important topics in DBMS. We majorly discuss the types of constraints in DBMS.
Lets begin with "What are the constraints in DBMS?"
What are the Constraints in DBMS?
Constraints in DBMS are the restrictions that are applied to data or operations on the data. This means that constraints allow only a particular kind of data to be inserted in the database or only some particular kind of operations to be performed on the data in the database.
Thus, constraints ensure the correctness of data in a Database Management System (DBMS).
Now, let us discuss the different types of constraints in DBMS.
Types of Constraints in DBMS
In relational databases, there are mainly 5 types of constraints in DBMS called relational constraints. They are as follows:
- Domain Constraints
- Key Constraints
- Entity Integrity Constraints
- Referential Integrity Constraints
- Tuple Uniqueness Constraints
We will discuss all the constraints in DBMS one by one.
Domain Constraints in DBMS
-
The domain means a range of values. In mathematics, the concept of Domain means the allowed values for a function.
-
Similarly, in DBMS, the Domain Constraint specifies the domain or set of values.
-
This is a constraint applied to attributes, not tuples. This means that it defines what values are allowed to be kept inside a particular column (attribute) for a table.
-
The domain constraint specifies that the value of an attribute must be an atomic value in its own domain.
Consider the following example table.
| Student ID | Student Name | Marks (in %) |
|---|---|---|
| 1 | Guneet | 90 |
| 2 | Ahan | 92 |
| 3 | Yash | 87 |
| 4 | Lavish | 90 |
| 5 | Ashish | 79 |
So, we can say that this is a valid table. This is because the student ID attribute can have only integers as ID and it does have only integers as ID. Also, the names can be strings only and the marks can be integers or floating values only. So, every attribute for every tuple in this table has its values within its domain.
Now, consider the table shown below.
| Student ID | Student Name | Marks (in %) |
|---|---|---|
| 1 | Guneet | 90 |
| 2 | Ahan | 92 |
| 3 | Yash | 87 |
| 4 | Lavish | A |
| 5 | Ashish | 79 |
Now, in the table above, the tuple with Student ID = 4 and name = “Lavish” has marks = A. This is not an integer or float value. So, the domain constraint is violated here.
Types of Domain constraints in DBMS
1. Not Null: The values that are not assigned or are unknown can be kept null. These can be the default values for an attribute if the answer is not known. For instance, in the above table, if we dont know the marks of the student with student id = 1, we can keep null in the marks attribute. However, certain attributes cannot be null. For instance, the Student ID is a must-known value and it can never be null as we can identify each student uniquely only with the help of Student ID. So, we can apply a “not null” constraint on the Student ID attribute in the above table.
Create table Students
(Student_id NUMBER not null,
Student_name varchar(30),
marks NUMBER);2. Check: Let us say we have a class of students. Now, the school decides that only the students with marks greater than 35% will be declared qualified in the current class. Also, they decide that only the results of those students will be entered in the table who have passed the class. So, they want to check whether the marks of a student are greater than 35% or not. If not, that student’s data will not be entered in the table. So, for this kind of constraint application, the Domain Constraint – Check is used.
Create table Students
(Student_id NUMBER not null,
Student_name varchar(30),
marks NUMBER check(marks > 35))So, this is the Domain Constraint in DBMS. Now, let us move ahead and study the next constraint i.e. the Tuple Uniqueness Constraint.
Tuple Uniqueness Constraint in DBMS
-
This is a very simple constraint. Tuple in DBMS means row or record.
-
As the name suggests, the tuple uniqueness constraint in DBMS specifies that each tuple in the table must be unique.
-
A tuple is said to be duplicate if all the corresponding attribute values of that tuple are present in some other tuple simultaneously in the table.
For instance, let us consider the table shown below.
| Student ID | Student Name | Marks (in %) |
|---|---|---|
| 1 | Guneet | 90 |
| 2 | Ahan | 92 |
| 3 | Yash | 87 |
| 4 | Lavish | 90 |
| 5 | Ashish | 79 |
In this table, all the tuples are unique. So, we can say that the tuple uniqueness constraint is not violated here.
Consider the table shown below.
| Student ID | Student Name | Marks (in %) |
|---|---|---|
| 1 | Guneet | 90 |
| 2 | Ahan | 92 |
| 1 | Guneet | 90 |
| 4 | Lavish | 90 |
| 5 | Ashish | 79 |
In the above table, tuple 1 and 3 have all the attribute values same. Thus these 2 tuples are not unique or we can simply say that they are duplicate tuples. So, the tuple uniqueness constraint is violated here.
We can use the UNIQUE keyword for applying the tuple uniqueness constraint.
Key Constraint in DBMS
- As the name suggests, this is a constraint applied on an attribute that we consider to be a primary key. So, the conditions for a primary key in a table is in fact this constraint.
- So, we know that a primary key cannot be null.
- Also, a primary key must be unique.
Consider the table shown below
| Student ID | Student Name | Marks (in %) |
|---|---|---|
| 1 | Guneet | 90 |
| 2 | Ahan | 92 |
| 3 | Yash | 87 |
| 4 | Lavish | 90 |
| 5 | Ashish | 79 |
Here, the Student_ID is a primary key. So, we can see that all the tuples have a unique value for Student_ID and there is no tuple in which the Student_ID attribute is null. So, in the above table, the attribute Student_ID satisfies the Key constraint.
Create table Students
(Student_ID NUMBER not null,
Student_name varchar(30),
marks NUMBER,
PRIMARY KEY (Student_ID))Consider the table shown below.
| Student ID | Student Name | Marks (in %) |
|---|---|---|
| 1 | Guneet | 90 |
| 2 | Ahan | 92 |
| 1 | Yash | 87 |
| 4 | Lavish | 90 |
| null | Ashish | 79 |
In the table above, the tuples 1 and 3 have value of Student_ID = 1. So, the primary key constraint is violated here as the values are not unique.
Also, in the 5th tuple, we can see that the value of Student_ID is null. This can’t be allowed as the Student_ID is the primary key. Hence, we can say that the Key constraint is violated there as well.
Entity Integrity Constraint in DBMS
- This is the same as the Key constraint. In fact, it is just a subset of the Key constraint.
- The key constraint states that the Primary Key attributes should be unique and must not contain null values.
- However, Entity Integrity Constraint states that any attribute of a Primary key must not be null.
- The perspective that this constraint holds is that if null values are allowed in the Primary key attributes, then there can be multiple null values. Hence, the constraint of a Primary Key being unique for every tuple will be violated.
Consider the table shown below.
| Student ID | Student Name | Marks (in %) |
|---|---|---|
| 1 | Guneet | 90 |
| 2 | Ahan | 92 |
| null | Yash | 87 |
| 4 | Lavish | 90 |
| 5 | Ashish | 79 |
Here, the third tuple has null value stored in the Student_ID attribute which is a Primary Key. Hence, the Entity Integrity constraint is violated here.
Referential Integrity Constraint in DBMS
- Referential integrity is a database concept that ensures the consistency and accuracy of data between related tables. It is maintained through the use of primary and foreign key.
- Referential integrity constraint is applied when a foreign key references the primary key of our table.
- This constraint can be summarized in one line i.e. the referencing attribute must be the subset of referred attribute.
- This means that a record or a tuple cannot be inserted in the referencing relation if it is not present in the referenced relation.
- Also, any record that is present in the referencing relation cannot be updated or deleted from the referenced relation.
Consider the tables shown below.
Students
| Student ID | Student Name | Marks (in %) | Dept_No |
|---|---|---|---|
| 1 | Guneet | 90 | D01 |
| 2 | Ahan | 92 | D02 |
| 3 | Yash | 87 | D01 |
| 4 | Lavish | 90 | D03 |
| 5 | Ashish | 79 | D02 |
Departments
| Dept_No | Dept_Name |
|---|---|
| D01 | CSE |
| D02 | IT |
| D03 | ECE |
Now, in the above tables (or relations), the relation “Students” references the relation “Departments”.
The above relation satisfies the referential integrity constraint.
Now, consider the relations shown below.
Students
| Student ID | Student Name | Marks (in %) | Dept_No |
|---|---|---|---|
| 1 | Guneet | 90 | D01 |
| 2 | Ahan | 92 | D02 |
| 3 | Yash | 87 | D01 |
| 4 | Lavish | 90 | D05 |
| 5 | Ashish | 79 | D02 |
Departments
| Dept_No | Dept_Name |
|---|---|
| D01 | CSE |
| D02 | IT |
| D03 | ECE |
Now, the tuple 4 in the “Students” relation violates referential integrity constraint as there is no department with id D05.
So, these are the different types of constraints in DBMS. We hope that you have understood the meaning and use of each of these constraints in DBMS. They are very essential to maintain the databases correctly. Constraints in DBMS is one of the most important topic in DBMS. We hope that you have understood everything in detail. See you again at PrepBytes. Also this is not the end, there are lots more to explore about constraints in DBMS. Think about that and explore more for constraints in DBMS.