Integrity Constraints

Definition

Integrity constraints are predefined rules in a database that restrict the type and format of data that can be stored, ensuring data accuracy, consistency, and validity across tables and relationships.

In simple terms, they are the conditions that database management systems enforce to prevent incorrect data from entering the database.

Main Content

1. Domain Integrity

Domain integrity ensures that each attribute in a table stores only valid values from a defined domain, meaning a permitted set of values, data type, range, or format.
It controls what kind of data is allowed in a column, such as numeric only, date only, non-empty values, or values within a specific range.

Domain integrity is one of the most basic and important forms of data validation. Every column in a database has a domain. For example, if a column is meant to store age, then the allowed values may be integers from 0 to 120. If the column is meant to store gender, the allowed values might be restricted to specific codes such as M, F, or O. If a product price field is set to a decimal type with a check condition, a negative value can be rejected.

Common mechanisms used to enforce domain integrity include:

Data types such as INT, VARCHAR, DATE, and DECIMAL
NOT NULL constraint to prevent missing values
CHECK constraint to restrict acceptable values
DEFAULT values for automatic assignment when no value is provided

Example:

CREATE TABLE Student (
    StudentID INT,
    Name VARCHAR(50) NOT NULL,
    Age INT CHECK (Age BETWEEN 18 AND 30)
);

In this example, Name cannot be null, and Age must be between 18 and 30. If a user tries to insert age 45, the database rejects it because it violates domain integrity.

Domain integrity is important because it prevents invalid data at the point of entry rather than allowing bad data to spread through the system.

2. Entity Integrity

Entity integrity ensures that every table has a unique identifier for each row, usually through a primary key, and that this key cannot contain null values.
It guarantees that each record is distinguishable from every other record in the table.

A relational table represents entities such as students, employees, products, or departments. To identify each row uniquely, a primary key is used. The entity integrity rule says that:

The primary key must be unique.
The primary key cannot be NULL.

This is necessary because if a row has no primary key value, the database cannot reliably identify it. If two rows have the same primary key, the database cannot distinguish between them.

Example:

CREATE TABLE Employee (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(50),
    Department VARCHAR(30)
);

Here, EmpID is the primary key. The database will not allow:

Two employees with the same EmpID
An employee record with a missing EmpID

This constraint is especially useful when updating or deleting specific records. Since each row is uniquely identifiable, operations can be performed accurately without affecting other rows.

Entity integrity also supports the logical structure of relational databases by making sure that every entity instance is represented unambiguously.

3. Referential Integrity

Referential integrity ensures that relationships between tables remain valid by requiring foreign key values to match an existing primary key value in the related table, or be null if allowed.
It prevents orphan records, which are records in one table that refer to non-existing records in another table.

This is one of the most important integrity constraints in relational databases because data is commonly split across multiple tables. For example, a Department table and an Employee table may be related. Each employee may belong to one department, so the employee table stores a DeptID as a foreign key. Referential integrity ensures that the DeptID in the employee table must exist in the department table.

Example:

CREATE TABLE Department (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50)
);

CREATE TABLE Employee (
    EmpID INT PRIMARY KEY,
    EmpName VARCHAR(50),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

If a department with DeptID = 10 does not exist, the database will not allow inserting an employee with DeptID = 10. This prevents inconsistent relationships.

Referential integrity also applies during deletion and update operations. If a referenced parent row is deleted, the database may:

Reject the deletion
Cascade the deletion to child rows
Set the foreign key to null
Set it to a default value

These actions are controlled by referential actions such as:

ON DELETE CASCADE
ON DELETE SET NULL
ON UPDATE CASCADE

Simple relationship view:

Department(DeptID)  ----<  Employee(DeptID)
    PK                    FK

This means one department can have many employees, but each employee must refer to a valid department.

Working / Process

Define the schema and rules
While designing the database, decide the data types, primary keys, foreign keys, and validation rules for each table.
Specify which attributes must be unique, which can be null, and which values are allowed.
Example: make StudentID a primary key, Email unique, and Age between 18 and 25.
Insert or modify data
When a user inserts, updates, or deletes a record, the DBMS checks whether the operation violates any integrity constraint.
If the data satisfies all rules, the operation is accepted.
If any rule is violated, the DBMS rejects the operation and returns an error message.
Enforce consistency automatically
The database engine continuously maintains integrity by validating each transaction.
If related data changes, the DBMS enforces cascading rules or blocks unsafe changes depending on the constraint definition.
This automatic enforcement keeps the database consistent even in multi-user environments.

Advantages / Applications

Ensures data accuracy by preventing invalid, duplicate, or incomplete values from being stored.
Maintains consistency across related tables, especially in large databases with multiple relationships.
Improves reliability of applications by making sure that reports, queries, and transactions are based on valid data.
Used in student management systems to ensure valid roll numbers, marks, attendance, and department references.
Used in banking systems to prevent invalid account numbers, negative balances where prohibited, and incorrect transaction references.
Used in inventory and e-commerce systems to ensure valid product IDs, stock quantities, order IDs, and customer references.

Summary

Integrity constraints are rules that keep database data valid and consistent.
They help prevent incorrect values, duplicate records, and broken relationships.
The main types are domain integrity, entity integrity, and referential integrity.
Important terms to remember: primary key, foreign key, check constraint, not null, unique, referential integrity, domain, cascade.