Types of Constraints in DBMS

Last Updated on July 21, 2023 by Mayank Dham

In the world of database management systems (DBMS), constraints play a vital role in maintaining the integrity and reliability of the stored data. Constraints define rules and conditions that govern the data within a database, ensuring accuracy, consistency, and adherence to business rules. Understanding the various types of constraints in DBMS is essential for database administrators, developers, and data professionals to design robust databases and enforce data integrity.

This article serves as a comprehensive guide, exploring the different types of constraints found in DBMS. We will delve into each constraint type, discussing its purpose, characteristics, and practical examples to illustrate their significance. Whether you are a beginner starting your journey in database management or an experienced professional seeking to refresh your knowledge, this article will provide you with a solid foundation in understanding and implementing constraints effectively. Lets begin with "What is Constraints in DBMS?", later on we will discuss types of constraints in DBMS, and key constraints in DBMS as well.

What are the Constraints in DBMS?

Constraints in DBMS (Database Management Systems) are rules or conditions that are applied to the data within a database to ensure data integrity, consistency, and adherence to business rules. They define limitations and requirements that the data must meet, thereby preventing the entry of invalid or inconsistent data. Constraints serve as a set of predefined rules that govern the behavior and relationships of the data in a database, helping to maintain its accuracy and reliability.

The purpose of constraints is to enforce data quality and prevent data inconsistencies, thereby enhancing the overall data integrity and reliability of the database. Constraints define boundaries for data values, relationships between entities, uniqueness requirements, and more. By enforcing constraints, DBMS can ensure that the data conforms to predefined standards and business rules, making the database more robust and reliable.

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:

1. Domain Constraints in DBMS
2. Key Constraints in DBMS
3. Entity Integrity Constraints in DBMS
4. Referential Integrity Constraints in DBMS
5. Tuple Uniqueness Constraints in DBMS

We will discuss all the constraints in DBMS one by one.

Domain Constraints in DBMS

1. The domain means a range of values. In mathematics, the concept of Domain means the allowed values for a function.

2. Similarly, in DBMS, the Domain Constraint specifies the domain or set of values.

3. 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.

4. 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

1. This is a very simple constraint. Tuple in DBMS means row or record.

2. As the name suggests, the tuple uniqueness constraint in DBMS specifies that each tuple in the table must be unique.

3. 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

1. 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.
2. So, we know that a primary key cannot be null.
3. 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

1. This is the same as the Key constraint. In fact, it is just a subset of the Key constraint.
2. The key constraint states that the Primary Key attributes should be unique and must not contain null values.
3. However, Entity Integrity Constraint states that any attribute of a Primary key must not be null.
4. 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

1. 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.
2. Referential integrity constraint is applied when a foreign key references the primary key of our table.
3. This constraint can be summarized in one line i.e. the referencing attribute must be the subset of referred attribute.
4. This means that a record or a tuple cannot be inserted in the referencing relation if it is not present in the referenced relation.
5. 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.

Conclusion
In conclusion, constraints in DBMS (Database Management Systems) are crucial components that ensure the integrity, consistency, and reliability of data stored within a database. They define rules and conditions that data must adhere to, preventing the entry of invalid or inconsistent values. Understanding different types of constraints is essential for designing robust databases and maintaining data quality. Key constraints in DBMS, such as entity integrity constraints and referential integrity constraints, enforce uniqueness and relationships between entities, respectively. Domain constraints in DBMS define the valid range of values that a particular attribute can have, ensuring data integrity by restricting the type and range of data stored in a column.

FAQs (Frequently Asked Questions) on Types of Constraints in DBMS:

Here are some FAQs related to key constraints in DBMS, domain constraints in DBMS, and types of constraints in DBMS.

Q1: What are key constraints in DBMS?
A1: Key constraints in DBMS include entity integrity constraints and referential integrity constraints. Entity integrity constraints ensure the uniqueness and integrity of primary keys, while referential integrity constraints establish and maintain relationships between tables through primary key-foreign key associations.

Q2: What are domain constraints in DBMS?
A2: Domain constraints define the valid range of values that a particular attribute can have. They restrict the type and range of data that can be stored in a column, ensuring data integrity and consistency.

Q3: What are the types of constraints in DBMS?
A3: The types of constraints in DBMS include entity integrity constraints, referential integrity constraints, domain constraints, check constraints, default constraints, and not-null constraints. Each type serves a specific purpose in maintaining data integrity and enforcing data quality.

Q4: Why are constraints important in DBMS?
A4: Constraints are important in DBMS as they enforce data integrity, maintain data consistency, prevent data anomalies, and ensure that the data within a database adheres to predefined standards and business rules. They guarantee the accuracy, reliability, and validity of the data, facilitating data-driven decision-making and the overall success of an application or system.

Q5: Can constraints be modified or removed in DBMS?
A5: Yes, constraints can be modified or removed in DBMS. Depending on the specific DBMS system and its capabilities, constraints can be added, modified, or dropped using appropriate SQL statements or through the DBMS management tools provided. It is important to exercise caution when modifying or removing constraints to avoid unintended consequences or data inconsistencies.