Integrity Constraints in DBMS

Overview

In database management systems (DBMS) there is a certain set of rules which are used to maintain the quality and consistency of data in the database. Every time there is an insertion, deletion, or updating of data in the database it is the responsibility of these integrity constraints to maintain the integrity of data and thus help to prevent accidental damage to the database.

Before reading this article, you should read the following DBMS topics:

What are Integrity Constraints in DBMS?

In Database Management Systems, integrity constraints are pre-defined set of rules that are applied on the table fields(columns) or relations to ensure that the overall validity, integrity, and consistency of the data present in the database table is maintained. Evaluation of all the conditions or rules mentioned in the integrity constraint is done every time a table insert, update, delete, or alter operation is performed. The data can be inserted, updated, deleted, or altered only if the result of the constraint comes out to be True. Thus, integrity constraints are useful in preventing any accidental damage to the database by an authorized user.

Types of Integrity Constraints

There are four types of integrity constraints in DBMS:

Domain Constraint

Entity Constraint

Referential Integrity Constraint

Key Constraint

Domain Constraint

Domain integrity constraint contains a certain set of rules or conditions to restrict the kind of attributes or values a column can hold in the database table. The data type of a domain can be string, integer, character, DateTime, currency, etc.

Example:

Consider a Student's table having Roll No, Name, Age, Class of students.

Roll No	Name	Age	Class
101	Adam	14	6
102	Steve	16	8
103	David	8	4
104	Bruce	18	12
105	Tim	6	A

In the above student's table, the value A in the last row last column violates the domain integrity constraint because the Class attribute contains only integer values while A is a character.

Entity Integrity Constraint

Entity Integrity Constraint is used to ensure that the primary key cannot be null. A primary key is used to identify individual records in a table and if the primary key has a null value, then we can't identify those records. There can be null values anywhere in the table except the primary key column.

Example:

Consider Employees table having Id, Name, and salary of employees

ID	Name	Salary
1101	Jackson	40000
1102	Harry	60000
1103	Steve	80000
1104	Ash	1800000
	James	36000

In the above employee's table, we can see that the ID column is the primary key and contains a null value in the last row which violates the entity integrity constraint.

Referential Integrity Constraint

Referential Integrity Constraint ensures that there must always exist a valid relationship between two relational database tables. This valid relationship between the two tables confirms that a foreign key exists in a table. It should always reference a corresponding value or attribute in the other table or be null.

Example:

Consider an Employee and a Department table where Dept_ID acts as a foreign key between the two tables

Employees Table

ID	Name	Salary	Dept_ID
1101	Jackson	40000	3
1102	Harry	60000	2
1103	Steve	80000	4
1104	Ash	1800000	3
1105	James	36000	1

Department Table

Dept_ID	Dept_Name
1	Sales
2	HR
3	Technical

In the above example, Dept_ID acts as a foreign key in the Employees table and a primary key in the Department table. Row having DeptID=4 violates the referential integrity constraint since DeptID 4 is not defined as a primary key column in the Departments table.

Key constraint

Keys are the set of entities that are used to identify an entity within its entity set uniquely. There could be multiple keys in a single entity set, but out of these multiple keys, only one key will be the primary key. A primary key can only contain unique and not null values in the relational database table.

Example:

Consider a student's table

Roll No	Name	Age	Class
101	Adam	14	6
102	Steve	16	8
103	David	8	4
104	Bruce	18	12
102	Tim	6	2

The last row of the student's table violates the key integrity constraint since Roll No 102 is repeated twice in the primary key column. A primary key must be unique and not null therefore duplicate values are not allowed in the Roll No column of the above student's table.

Conclusion

Integrity Constraints in Database Management Systems are the set of pre-defined rules responsible for maintaining the quality and consistency of data in the database.
Evaluation against the rules mentioned in the integrity constraint is done every time an insert, update, delete, or alter operation is performed on the table.
Integrity Constraints in DBMS are of 4 types: