Complex Integrity Constraints in SQL Explained with Real Examples

Complex Integrity Constraints in SQL are advanced rules that database systems use to keep data clean, accurate, and reliable when standard checks fall short. While simple constraints protect individual columns, like ensuring an age is never negative, complex integrity constraints enforce intricate business rules that span across multiple rows, separate tables, or dynamic calculations.

By using mechanisms like check constraint, triggers and assertions, SQL complex integrity constraints automatically evaluate cross-table dependencies and aggregate limits, blocking any transaction that violates your data logic before it can corrupt your database.

What is an integrity constraint?

An integrity constraint is a rule enforced by a database to ensure that all stored data remains accurate, consistent, and trustworthy. Think of it as a set of strict guardrails or security checks that prevent invalid information from accidentally being added, modified, or deleted.

For example, an integrity constraint might rule that a customer’s age cannot be a negative number, every user must have a unique email address, or an order cannot exist without being linked to a valid customer.

If someone tries to enter data that breaks one of these rules, the database automatically blocks the entry, ensuring the entire system stays clean and reliable over time.

What are complex integrity constraints in SQL?

A complex integrity constraint in SQL is a custom rule that spans across multiple rows, tables, or external conditions to ensure data accuracy when standard constraints like NOT NULL or UNIQUE are not enough.

While basic constraints check simple rules on single columns (like ensuring an age is positive), complex constraints handle intricate business logic such as enforcing that an employee’s salary cannot exceed their manager’s salary, or ensuring a customer cannot place more than five pending orders in a single day.

In SQL, these rules are typically enforced using database triggers or assertions, which automatically run background checks during an insertion or update and reject the transaction if any of the specified logic is violated.

Complex integrity constraints with examples

Complex integrity constraints in SQL are business rules that evaluate conditions across multiple rows or tables to keep data accurate and consistent. Unlike basic constraints (such as NOT NULL or simple single-column CHECK clauses), complex constraints handle conditions that require evaluating dynamic conditions, subqueries, or cross-table relationships.

In relational databases, these are implemented using:

• Table-level CHECK constraints with subqueries or SQL assertions (though many database engines prefer triggers for performance).
• Database Triggers, which intercept INSERT, UPDATE, or DELETE operations and reject changes if business logic is violated.

Let’s discuss about complex integrity constraints with examples using our university database schema and tables.

CHECK constraint

A CHECK constraint is a standard database rule applied to individual columns or single rows to ensure that data values meet specific criteria (such as ensuring an age is positive or a status string matches predefined choices).

• Description: Defines a specific logical condition that every row inserted or updated within a single table must satisfy.
• Importance: Prevents invalid, out-of-range, or corrupted domain values from entering individual table fields.
• Example Rule: In the Dependent table, a dependent’s Age must be greater than zero and less than 100.

SQL Implementation

ALTER TABLE Dependent

ADD CONSTRAINT chk_dependent_age CHECK (Age > 0 AND Age < 100);

Allowed Query & Output Table

Inserting a dependent record for Prof_ID = 6 (Dr. Fiona Gallagher) with a valid Age of 12:

INSERT INTO Dependent (Prof_ID, Dep_Name, Age)

VALUES (6, 'Liam Gallagher', 12);




SELECT * FROM Dependent WHERE Prof_ID = 6;

Output:

Disallowed Query & Output Table

Attempting to insert a dependent with an invalid negative age (Age = -5):

INSERT INTO Dependent (Prof_ID, Dep_Name, Age)

VALUES (6, 'Invalid Dependent', -5);

Output:

ERROR: Check constraint ‘chk_dependent_age’ is violated.

SQL Assertion

An SQL assertion is a database-level integrity constraint that evaluates complex, multi-table conditions across the entire database to maintain multi-entity consistency.

• Description: A standalone database schema object that defines a general boolean search condition spanning multiple tables, preventing any database transaction that causes the assertion condition to evaluate to false.
• Importance: Enforces cross-table dynamic rules declaratively without requiring procedural trigger code, ensuring global database consistency.
• Example Rule: The total number of Course records assigned to any single Prof_ID across the database cannot exceed 2.

CREATE ASSERTION assert_max_courses_per_prof CHECK (

    NOT EXISTS (

        SELECT Prof_ID

        FROM Course

        GROUP BY Prof_ID

        HAVING COUNT(Course_ID) > 2

    )

);

Allowed Query & Output Table

Assigning a new course (Course_ID = 207) to Prof_ID = 1 (Dr. Alice Smith), who currently teaches only 1 course (Course_ID = 201):

INSERT INTO Course (Course_ID, Course_Name, Prof_ID)

VALUES (207, 'Advanced Databases', 1);




SELECT Course_ID, Course_Name, Prof_ID FROM Course WHERE Prof_ID = 1;

Output:

Disallowed Query & Output Table

Attempting to assign a third course (Course_ID = 208) to Prof_ID = 1 (who now teaches 2 courses: 201 and 207):

INSERT INTO Course (Course_ID, Course_Name, Prof_ID)

VALUES (208, 'Cloud Computing', 1);

Output:

ERROR: Assertion ‘assert_max_courses_per_prof’ is violated.

Triggers

A trigger is a special type of stored procedure in a database system that automatically executes or fires in response to specific data modification events such as INSERT, UPDATE, or DELETE operations on a designated table.

Unlike standard stored procedures that must be called manually, a trigger runs implicitly whenever the specified event occurs, making it an essential tool for enforcing complex integrity constraints, maintaining detailed audit logs, and automatically recalculating summary values across related tables.

Let’s see different ways in which triggers can be used to enforce complex integrity constraints.

Cross-Table Validation (Table Comparison Constraint)

• Description: Ensures that data inserted into one table matches corresponding reference values in another related table.
• Importance: Prevents mismatched or orphan assignments, ensuring multi-table relationships remain logically synchronized.
• Example Rule: A student assigned to a project in Project_Assignment must be taught by the professor who actually teaches that specific Course_ID in the Course table.

SQL Implementation

CREATE TRIGGER chk_project_assignment_prof

BEFORE INSERT OR UPDATE ON Project_Assignment

FOR EACH ROW

BEGIN

    IF NOT EXISTS (

        SELECT 1 FROM Course

        WHERE Course_ID = NEW.Course_ID

          AND Prof_ID = NEW.Prof_ID

    ) THEN

        SIGNAL SQLSTATE '45000'

        SET MESSAGE_TEXT = 'Error: Assigned professor does not teach this course.';

    END IF;

END;

Allowed Query & Output Table

Inserting a valid assignment (Dr. Alice Smith, Prof_ID = 1, teaches Course_ID = 201):

INSERT INTO Project_Assignment (Student_ID, Course_ID, Prof_ID)

VALUES (1006, 201, 1);




SELECT * FROM Project_Assignment WHERE Student_ID = 1006;

Output:

Disallowed Query & Output Table

Attempting to assign Prof_ID = 3 (Dr. Charlie Brown) to Course_ID = 201 (Database Systems, taught by Prof_ID = 1):

INSERT INTO Project_Assignment (Student_ID, Course_ID, Prof_ID)

VALUES (1006, 201, 3);

Output:

ERROR 45000: Assigned professor does not teach this course.

Multi-Table Conditional Dependency (Role Alignment Constraint)

• Description: Restricts primary actions in a table based on attributes defined in another table.
• Importance: Keeps organizational hierarchies valid (e.g., ensuring a department head actually belongs to that department).
• Example Rule: A professor assigned as a Chair_Prof_ID in Department must belong to that same Dept_ID in the Professor table.

SQL Implementation

CREATE TRIGGER chk_department_chair_dept

BEFORE INSERT OR UPDATE ON Department

FOR EACH ROW

BEGIN

    IF NEW.Chair_Prof_ID IS NOT NULL AND NOT EXISTS (

        SELECT 1 FROM Professor

        WHERE Prof_ID = NEW.Chair_Prof_ID

          AND Dept_ID = NEW.Dept_ID

    ) THEN

        SIGNAL SQLSTATE '45000'

        SET MESSAGE_TEXT = 'Error: Department Chair must belong to the department.';

    END IF;

END;

Allowed Query & Output Table

Assigning Prof_ID = 2 (Dr. Bob Jones, who is in Dept_ID = 101) as the chair of Dept_ID = 101:

UPDATE Department

SET Chair_Prof_ID = 2

WHERE Dept_ID = 101;




SELECT Dept_ID, Dept_Name, Chair_Prof_ID FROM Department WHERE Dept_ID = 101;

Output:

Disallowed Query & Output Table

Attempting to assign Prof_ID = 4 (Dr. Diana Prince, who is in Dept_ID = 103) as chair of Dept_ID = 101:

UPDATE Department

SET Chair_Prof_ID = 4

WHERE Dept_ID = 101;

Output:

ERROR 45000: Department Chair must belong to the department.

Aggregation & Limit Constraint (Threshold Limit)

• Description: Restricts new entries based on aggregate metrics (counts, sums, or averages) across related records.
• Importance: Prevents system over-allocation, like student course overload or overbooking.
• Example Rule: A student cannot enroll in more than 2 courses in the Enrollment table.

SQL Implementation

CREATE TRIGGER chk_max_enrollment

BEFORE INSERT ON Enrollment

FOR EACH ROW

BEGIN

    DECLARE course_count INT;

   

    SELECT COUNT(*) INTO course_count

    FROM Enrollment

    WHERE Student_ID = NEW.Student_ID;

   

    IF course_count >= 2 THEN

        SIGNAL SQLSTATE '45000'

        SET MESSAGE_TEXT = 'Error: Student cannot be enrolled in more than 2 courses.';

    END IF;

END;

Allowed Query & Output Table

Enrolling Student_ID = 1002 (currently taking 1 course) into Course_ID = 202:

INSERT INTO Enrollment (Student_ID, Course_ID)

VALUES (1002, 202);




SELECT * FROM Enrollment WHERE Student_ID = 1002;

Output:

Disallowed Query & Output Table

Attempting to enroll Student_ID = 1001 (already enrolled in courses 201 and 202) into a third course (Course_ID = 203):

INSERT INTO Enrollment (Student_ID, Course_ID)

VALUES (1001, 203);

Output:

ERROR 45000: Student cannot be enrolled in more than 2 courses.

Suryateja Pericherla

Mr. P.S.Suryateja, also known as Suryateja Pericherla, is at present a Research Scholar (full-time Ph.D.) in the Dept. of Computer Science & Systems Engineering at Andhra University, Visakhapatnam. Previously worked as an Associate Professor in the Dept. of CSE at Vishnu Institute of Technology, India.

He has 14+ years of teaching experience and is an individual researcher whose research interests are Cloud Computing, Internet of Things, Computer Security, Network Security and Blockchain.

He is a member of professional societies like IEEE, ACM, CSI and ISCA. He published several research papers which are indexed by SCIE, WoS, Scopus, Springer and others.