1. Introduction to Integrity in DBMS
Data Integrity in a Database Management System (DBMS) refers to the accuracy, consistency, and reliability of data. It ensures that data is not modified, deleted, or inserted in an incorrect way, maintaining the correctness of the database over its lifecycle.
Why is Data Integrity Important?
✅ Prevents duplicate, inconsistent, or incorrect data.
✅ Ensures data accuracy and consistency across tables.
✅ Maintains reliability of database transactions.
✅ Protects relationships between tables using constraints.
2. Types of Data Integrity
There are several types of integrity in a database, enforced using constraints and rules.
1. Entity Integrity
- Ensures each record (row) in a table is uniquely identified.
- Primary Key (PK) helps enforce entity integrity.
✔ Example: Every employee must have a unique Employee ID.
✔ Implementation in SQL:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
Department VARCHAR(50)
);
✅ Prevents duplicate Employee IDs.
2. Referential Integrity
- Ensures relationships between tables remain consistent.
- Foreign Key (FK) is used to maintain referential integrity.
✔ Example: Every order must be linked to an existing customer.
✔ Implementation in SQL:
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(100)
);
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
Amount DECIMAL(10,2),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE CASCADE
);
✅ Prevents orders from referencing non-existent customers.
3. Domain Integrity
- Ensures data in a column follows a defined format.
- Enforced using CHECK, NOT NULL, and DEFAULT constraints.
✔ Example: Employee salary must be greater than zero.
✔ Implementation in SQL:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
Salary DECIMAL(10,2) CHECK (Salary > 0)
);
✅ Prevents invalid salary values (e.g., negative salary).
4. User-Defined Integrity
- Enforces business-specific rules that are not covered by the other constraints.
✔ Example:
- An employee must be at least 18 years old.
- A product cannot be sold if stock is zero.
✔ Implementation in SQL:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT CHECK (Age >= 18)
);
✅ Prevents employees younger than 18 from being added.
3. Integrity Constraints in DBMS
To enforce integrity, DBMS provides integrity constraints that restrict invalid data entry.
| Constraint | Description | Example |
|---|---|---|
| PRIMARY KEY | Uniquely identifies each record | EmployeeID INT PRIMARY KEY |
| FOREIGN KEY | Ensures referential integrity between tables | FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) |
| NOT NULL | Prevents null values in a column | Name VARCHAR(100) NOT NULL |
| UNIQUE | Ensures values in a column are distinct | Email VARCHAR(255) UNIQUE |
| CHECK | Validates conditions before inserting data | CHECK (Age >= 18) |
| DEFAULT | Assigns a default value if no value is provided | Status VARCHAR(10) DEFAULT 'Active' |
✔ Example of Multiple Constraints in SQL:
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL UNIQUE,
Price DECIMAL(10,2) CHECK (Price > 0),
Stock INT DEFAULT 0
);
4. Data Control in DBMS
Database control mechanisms ensure only authorized users can access, modify, or delete data.
1. Access Control
- Defines who can access and modify the database.
- Uses roles, privileges, and user authentication.
✔ Example: Granting Read-Only Access
GRANT SELECT ON Employees TO 'read_user'@'localhost';
✅ Prevents unauthorized modifications.
2. Transaction Control (ACID Properties)
To maintain data integrity, transactions follow ACID properties:
| Property | Description | Example |
|---|---|---|
| Atomicity | Entire transaction succeeds or fails completely | Bank Transfer: If deducting from A fails, do not add to B |
| Consistency | Database remains in a valid state | No foreign key violations |
| Isolation | Transactions execute independently | No dirty reads from uncommitted transactions |
| Durability | Committed data is permanently stored | Data persists even after system crash |
✔ Example of a Transaction in SQL:
START TRANSACTION;
UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 2;
COMMIT;
✅ Ensures atomic and durable bank transfers.
3. Concurrency Control
- Prevents conflicts when multiple users access the database at the same time.
- Uses locks, timestamps, and multi-version concurrency control (MVCC).
✔ Example: Locking a Table for Safe Update
LOCK TABLE Employees IN EXCLUSIVE MODE;
UPDATE Employees SET Salary = Salary + 500 WHERE EmployeeID = 1;
UNLOCK TABLES;
✅ Prevents data inconsistencies caused by concurrent updates.
4. Database Auditing & Monitoring
- Tracks who accessed or modified the database.
- Helps detect unauthorized access attempts.
✔ Example: Enabling Auditing in Oracle
AUDIT SELECT, UPDATE ON Employees BY ACCESS;
✅ Keeps a log of database activity.
5. Summary of Integrity & Control in DBMS
| Feature | Purpose |
|---|---|
| Entity Integrity | Ensures unique identification of records (Primary Key). |
| Referential Integrity | Maintains relationships between tables (Foreign Key). |
| Domain Integrity | Restricts values within a valid range (CHECK, NOT NULL). |
| User-Defined Integrity | Enforces custom business rules. |
| Transaction Control (ACID) | Ensures reliable and consistent transactions. |
| Access Control | Grants permissions to specific users (GRANT, REVOKE). |
| Concurrency Control | Manages multiple users accessing data simultaneously. |
| Database Auditing | Tracks and monitors database changes. |
🔹 Integrity ensures accurate, consistent, and valid data.
🔹 Control mechanisms protect data from unauthorized access and corruption.