Integrity Constraints in DBMS

quiz
Challenge Inside! : Find out where you stand! Try quiz, solve problems & win rewards!

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:

Scope of the article

This article explains the Integrity Constraints in a database management system. We also learn about the four major types of integrity constraints that are implemented in a DBMS.

The article explains in-depth all four types of integrity constraints along with examples.

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

Types of Integrity Constraints

There are four types of integrity constraints in DBMS:

  1. Domain Constraint
  2. Entity Constraint
  3. Referential Integrity Constraint
  4. 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 NoNameAgeClass
101Adam146
102Steve168
103David84
104Bruce1812
105Tim6A

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

IDNameSalary
1101Jackson40000
1102Harry60000
1103Steve80000
1104Ash1800000
James36000

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

IDNameSalaryDept_ID
1101Jackson400003
1102Harry600002
1103Steve800004
1104Ash18000003
1105James360001

Department Table

Dept_IDDept_Name
1Sales
2HR
3Technical

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 NoNameAgeClass
101Adam146
102Steve168
103David84
104Bruce1812
102Tim62

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:
  1. Domain Constraint
  2. Entity Constraint
  3. Referential Integrity Constraint
  4. Key Constraint
Challenge Time!
quiz
quiz
Time to test your skills and win rewards! Note: Rewards will be credited after the next product update.
Free Courses by top Scaler instructors
certificate icon
Certificates
DBMS Tutorial
This program includes modules that cover the basics to advance constructs of DBMS Tutorial. The highly interactive and curated modules are designed to help you become a master of this language.'
If you’re a learning enthusiast, this is for you.
Module Certificate
Criteria
Upon successful completion of all the modules in the hub, you will be eligible for a certificate.
You need to sign in, in the beginning, to track your progress and get your certificate.
rcbGet a Free personalized Career Roadmap from