Integrity Constraints in DBMS
Learn via video course
FreeOverview
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
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:
- Domain Constraint
- Entity Constraint
- Referential Integrity Constraint
- Key Constraint