Integrity Constraints in DBMS

Ensuring Data Accuracy, Consistency, and Reliability

What are Integrity Constraints?

Integrity constraints are rules and conditions applied to database tables to ensure the accuracy, consistency, and reliability of data stored in a DBMS. They prevent invalid data entry and maintain relationships between data.

1. Domain Constraints

Defining Valid Range of Values for Attributes

Domain Constraints Include:

Data Type Constraints: Specify the type of data (INT, VARCHAR, DATE, etc.)
NOT NULL Constraints: Ensure mandatory fields are not empty
CHECK Constraints: Define specific value ranges or conditions
Default Values: Provide default values when none specified

Example: Student Registration System

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Age INT CHECK (Age >= 18 AND Age <= 65),
    Email VARCHAR(100) CHECK (Email LIKE '%@%.%'),
    Gender CHAR(1) CHECK (Gender IN ('M', 'F', 'O')),
    GPA DECIMAL(3,2) CHECK (GPA >= 0.0 AND GPA <= 4.0),
    RegistrationDate DATE DEFAULT (CURDATE())
);

2. Entity Integrity Constraints

Ensuring Unique Identity for Each Record

Entity Integrity Rules:

Primary Key Uniqueness: No two rows can have the same primary key value
Primary Key Non-null: Primary key values cannot be NULL
Immutability: Primary key values should not change once assigned
Single Primary Key: Each table can have only one primary key

Example: Employee Database

EmployeeID (PK)	Name	Department	Salary
E001	John Smith	IT	65000
E002	Mary Johnson	HR	58000
NULL	Invalid Entry	Finance	62000

Violation: The third row violates entity integrity because EmployeeID is NULL.

3. Key Constraints

Managing Uniqueness and Relationships

Types of Key Constraints:

Primary Key: Uniquely identifies each record, cannot be NULL
Unique Key: Ensures uniqueness, allows one NULL value
Candidate Key: Any attribute that could serve as primary key
Composite Key: Primary key made of multiple columns

Example: Product Catalog

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,           -- Primary Key
    ProductCode VARCHAR(20) UNIQUE,     -- Unique Key
    Barcode VARCHAR(50) UNIQUE,         -- Another Unique Key
    ProductName VARCHAR(100) NOT NULL,
    Price DECIMAL(10,2) CHECK (Price > 0),
    CategoryID INT,
    
    -- Composite unique constraint
    UNIQUE (ProductName, CategoryID)
);

-- Example of Composite Primary Key
CREATE TABLE OrderItems (
    OrderID INT,
    ProductID INT,
    Quantity INT NOT NULL,
    UnitPrice DECIMAL(10,2),
    
    PRIMARY KEY (OrderID, ProductID)    -- Composite Primary Key
);

4. Referential Integrity Constraints

Maintaining Consistency Between Related Tables

Referential Integrity Rules:

Foreign Key Rule: FK value must exist in referenced table or be NULL
CASCADE: Automatically propagate changes to dependent records
SET NULL: Set FK to NULL when referenced record is deleted
RESTRICT: Prevent deletion if dependent records exist

Example: Order Management System

-- Parent Table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL,
    Email VARCHAR(100) UNIQUE
);

-- Child Table with Foreign Key
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    TotalAmount DECIMAL(10,2),
    
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
        ON DELETE CASCADE
        ON UPDATE RESTRICT
);

-- Another level of relationship
CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)
        ON DELETE CASCADE,
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
        ON DELETE RESTRICT
);

Constraint Violations and Handling

What Happens When Constraints Are Violated

Constraint Type	Violation Example	System Response	How to Handle
Domain	Age = -5 (negative age)	INSERT/UPDATE rejected	Validate data before insertion
Entity Integrity	Primary Key = NULL	INSERT rejected	Ensure PK values are generated
Key	Duplicate primary key	INSERT rejected	Use auto-increment or check existence
Referential	FK references non-existent PK	INSERT/UPDATE rejected	Ensure referenced record exists first

                    Best Practices:
                    Design constraints during database schema design phase
Use appropriate referential actions (CASCADE, SET NULL, RESTRICT)
Implement application-level validation as first line of defense
Monitor constraint violations for data quality assessment
Document all constraints for maintenance teams

                

Advanced Integrity Concepts

Complex Constraints and Real-world Scenarios

Complex CHECK Constraints

-- Multi-column check constraint
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    HireDate DATE,
    RetireDate DATE,
    Salary DECIMAL(10,2),
    Department VARCHAR(50),
    
    -- Complex constraints
    CHECK (RetireDate IS NULL OR RetireDate > HireDate),
    CHECK (Salary > 0),
    CHECK (Department IN ('IT', 'HR', 'Finance', 'Marketing', 'Operations'))
);

-- Conditional constraints
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Age INT,
    Grade VARCHAR(2),
    
    -- Grade must be appropriate for age
    CHECK (
        (Age >= 18 AND Grade IN ('A', 'B', 'C', 'D', 'F')) OR
        (Age < 18 AND Grade IN ('1', '2', '3', '4', '5'))
    )
);

Assertion-like Constraints (Database Level)

-- Trigger-based constraint: Ensure total enrollment doesn't exceed capacity
DELIMITER //
CREATE TRIGGER check_enrollment_capacity
BEFORE INSERT ON Enrollments
FOR EACH ROW
BEGIN
    DECLARE current_count INT;
    DECLARE max_capacity INT;
    
    SELECT COUNT(*) INTO current_count 
    FROM Enrollments 
    WHERE CourseID = NEW.CourseID;
    
    SELECT Capacity INTO max_capacity 
    FROM Courses 
    WHERE CourseID = NEW.CourseID;
    
    IF current_count >= max_capacity THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'Course capacity exceeded';
    END IF;
END//
DELIMITER ;

GATE Level Practice Questions

Test Your Understanding

1. Which of the following statements about referential integrity is FALSE?

A foreign key value must either be NULL or match an existing primary key value in the referenced table
CASCADE action automatically updates/deletes dependent records when referenced record changes
A table can have multiple foreign keys but only one can have CASCADE action
RESTRICT action prevents deletion of referenced record if dependent records exist

2. In the context of entity integrity, which statement is correct?

A table can have multiple primary keys to ensure better uniqueness
Primary key attributes cannot contain NULL values and must be unique
Entity integrity only applies to tables with foreign key relationships
Primary key values can be modified frequently without affecting integrity

3. Consider the following schema:
STUDENT(SID, Name, Age)
COURSE(CID, Title, Credits)
ENROLLMENT(SID, CID, Grade)

Which constraint would prevent a student from enrolling in a non-existent course?

Domain constraint on CID in ENROLLMENT
Entity integrity constraint on COURSE table
Referential integrity constraint: ENROLLMENT.CID references COURSE.CID
Key constraint on ENROLLMENT table

4. Which of the following is NOT a characteristic of domain constraints?

They specify the data type of an attribute
They can include CHECK constraints for value validation
They can enforce NOT NULL requirements
They ensure uniqueness across multiple tables

5. In a relation R(A, B, C, D) with functional dependencies: A → B, C → D, and (A, C) is the primary key. If we try to insert a tuple with A=1, B=2, C=3, D=4 when a tuple (1, 5, 6, 7) already exists, what type of constraint violation occurs?

Key constraint violation due to duplicate key value
Domain constraint violation
Referential integrity violation
No violation occurs

6. Let R(a,b,c) and S(d,e,f) be two relations in which d is the foreign key of S that refers to the primary key of R. Consider the following four operations on R and S:
I. Insert into R
II. Insert into S
III. Delete from R
IV. Delete from S

Which of the following can cause violation of the referential integrity constraint above?

Both I and IV
Both II and III
All of these
None of these