DBMS Integrity Constraints – Full Details with Examples

What are Integrity Constraints?

Integrity constraints are rules enforced on database columns to ensure that the data remains accurate, valid, and consistent.
They protect the database from corrupt or invalid data entry.

Integrity constraints are defined at the time of table creation using SQL commands.

Types of Integrity Constraints in DBMS

There are five major types of integrity constraints:

1. Domain Constraint

• Ensures that all values in a column are of the same data type and optionally within a defined range.
• Prevents invalid data like entering text into a numeric column.

Example:

CREATE TABLE Students (
    student_id INT,
    name VARCHAR(100),
    age INT CHECK (age >= 5 AND age <= 100)
);

✔️ Only values between 5 and 100 can be entered in the age column.

2. Entity Integrity Constraint

• Applied using a Primary Key.
• Ensures that each row in a table is unique and identifiable.
• The Primary Key column cannot be NULL or duplicated.

Example:

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

❌ You cannot insert two students with the same student_id.
❌ student_id cannot be NULL.

3. Referential Integrity Constraint

• Enforced using a Foreign Key.
• Ensures that a value in one table must exist in another table.
• Maintains relationships between tables.

Example:

CREATE TABLE Departments (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE Employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

✔️ An employee can only be added with a valid dept_id that exists in the Departments table.

4. Key Constraint

• Ensures that values in the key column are unique.
• Applied using PRIMARY KEY or UNIQUE constraints.
• A table can have only one PRIMARY KEY but multiple UNIQUE keys.

Example:

CREATE TABLE Users (
    user_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

❌ No two users can have the same email.

5. NOT NULL Constraint

• Ensures that a column cannot have NULL values.
• Used when a field is mandatory.

Example:

CREATE TABLE Employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL
);

❌ You must enter a value for emp_name. It cannot be left empty.

Summary Table

Additional Integrity Options

You can also define ON DELETE CASCADE, ON UPDATE SET NULL, etc., when defining foreign keys:

FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
ON DELETE CASCADE
ON UPDATE CASCADE

✅ These help maintain data consistency during updates and deletions.

Importance of Integrity Constraints

• ✅ Ensures data correctness
• ✅ Prevents data duplication
• ✅ Maintains meaningful relationships
• ✅ Supports business rules
• ✅ Protects against accidental data loss