Skip to main content

Integrity

Integrity ensures that the data stored in a database is accurate, consistent, and reliable.

There are several types of integrity constraints:

1. Entity Integrity​

  • Every table must have a primary key that uniquely identifies each row.
  • The primary key cannot be NULL.

Example:

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

Ensures that each student record has a unique and non-null ID.

2. Referential Integrity​

  • Foreign keys must correctly reference primary keys in related tables.
  • Prevents invalid references.

Example:

CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

CREATE TABLE Enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

You cannot enroll a student in a course that does not exist.

3. Domain Integrity​

  • Ensures that attributes (columns) have valid values based on data type, constraints, and rules.

Example:

CREATE TABLE Employees (
    emp_id INT PRIMARY KEY,
    name VARCHAR(100),
    age INT CHECK (age >= 18),  -- Age must be at least 18
    salary DECIMAL(10,2) CHECK (salary > 0) -- Salary must be positive
);

Prevents invalid data like negative salary or underage employees.

4. User-Defined Integrity​

  • Business-specific rules that are not covered by the above categories.
  • Implemented using CHECK constraints, triggers, or stored procedures.

Example:

ALTER TABLE Accounts
ADD CONSTRAINT chk_min_balance CHECK (balance >= 1000);

Ensures that every account maintains at least a 1000-unit minimum balance.

Putting It All Together​

  • ACID properties → Focus on transaction reliability.
  • Integrity constraints → Focus on data correctness at the design level.

Example: Suppose we’re designing a Banking System:

  • Integrity constraints ensure that:
    • Every account has a unique ID (entity integrity).
    • Transfers reference valid accounts (referential integrity).
    • Balances can’t be negative (domain integrity).
  • ACID ensures that:
    • Transfers complete fully or not at all (atomicity).
    • The total sum of balances remains consistent (consistency).
    • Multiple transfers don’t interfere (isolation).
    • Once committed, transfers are permanent (durability).