Integrity Constraints in DBMS

Introduction

Integrity constraints are essential rules applied to database tables to ensure data accuracy, consistency, and validity. They prevent invalid operations that could corrupt the database or violate logical rules, making them a cornerstone of robust database design.

Types of Integrity Constraints

1. Entity Integrity Constraint

The Entity Integrity Constraint ensures that every table has a primary key to uniquely identify each row.

• Primary Key Rule: Every table must have a primary key.
• Uniqueness Rule: The primary key must be unique across all rows.
• Non-null Rule: The primary key cannot contain null values.

+-------------+--------------+---------------------+
| Customer ID | Customer Name | Customer Address    |
+-------------+--------------+---------------------+
| 1           | John Doe      | New York            |
| 2           | Jane Smith    | Los Angeles         |
+-------------+--------------+---------------------+

2. Domain Integrity Constraint

The Domain Integrity Constraint defines the domain (range or format) of valid values for a column.

• Example: CustomerID must be an integer between 1 and 100,000.
• SQL Syntax:

CREATE TABLE Customer (
    CustomerID INT CHECK (CustomerID BETWEEN 1 AND 100000),
    Email VARCHAR(100) CHECK (Email LIKE '%_@_%._%')
);

3. User-Defined Integrity Constraints

These constraints are specific to business logic or application requirements.

CREATE TABLE OrderDetails (
    OrderID INT,
    ProductID INT,
    Quantity INT CHECK (Quantity <= 5)
);

4. Referential Integrity Constraint

Ensures that foreign keys in one table reference valid rows in another table.

• Referenced Table: The table containing the primary key.
• Referencing Table: The table with the foreign key.

Customer Table:
+-------------+--------------+
| Customer ID | Customer Name|
+-------------+--------------+
| 1           | John Doe     |
| 2           | Jane Smith   |
+-------------+--------------+

Orders Table:
+------------+-------------+---------+
| Order ID   | Customer ID | Product |
+------------+-------------+---------+
| 101        | 1           | Laptop  |
| 102        | 3           | Phone   | <-- Invalid if CustomerID 3 does not exist.
+------------+-------------+---------+

Actions on Referential Integrity Violations:

• On Delete No Action: Prevents deletion of referenced rows.
• On Delete Cascade: Deletes referencing rows when a referenced row is deleted.
• On Delete Set Null: Sets foreign key values to NULL when the referenced row is deleted.
• On Update Cascade: Updates referencing rows when the primary key of a referenced row is updated.

5. Multi-level Cascades

A multi-level cascade occurs when an action (delete or update) on one table triggers cascading changes across multiple tables.

+-------+--------+
| EmpID | MgrID  |
+-------+--------+
| 1     | NULL   |
| 2     | 1      |
| 3     | 2      |
| 4     | 3      |
+-------+--------+

MgrID references EmpID.
Deleting EmpID = 1 with ON DELETE CASCADE deletes:
EmpID = 2 → EmpID = 3 → EmpID = 4.

Summary of Integrity Constraints

Conclusion

Integrity constraints are critical for maintaining data accuracy and reliability. Proper implementation of these constraints ensures robust database design, prevents errors, and maintains data consistency. However, constraints like ON DELETE CASCADE must be used cautiously to avoid unintended data loss.