Relational Integrity Constraints

In a Relational Database Management System (RDBMS), integrity constraints are rules that enforce data accuracy, consistency, and reliability. These constraints ensure that the database remains error-free, even when multiple users insert, update, or delete data.

Without integrity constraints, databases could suffer from:

❌ Duplicate records

❌ Invalid relationships (e.g., an order without a customer)

❌ Inconsistent data (e.g., negative age values)

In this article, we’ll explore the types of relational integrity constraints, their importance, and real-world examples.

Before reading this article, you should read the following DBMS topics:

What are Relational Integrity Constraints?

Relational Integrity Constraints are rules that ensure data in a database remains accurate, consistent, and reliable. They prevent invalid entries, broken relationships, or illogical data by enforcing conditions on tables, columns, and relationships.

For example, a primary key ensures each row is unique, a foreign key guarantees valid links between tables, and a check constraint blocks impossible values (like negative ages). These constraints act as "guardrails" to maintain data quality, whether during insertions, updates, or deletions.

Types of Relational Integrity Constraints.

There are four types of Relational Integrity Constraints in DBMS:

Entity Integrity Constraint.
Referential Integrity Constraint.
Domain Integrity Constraint.
Key Constraints (Uniqueness).

1. Entity Integrity Constraint.

Entity Integrity Constraint ensures that each row in a table is uniquely identifiable and never left empty.

It enforces two key rules:

Primary Key (PK) must be unique (no duplicate values).
PK cannot be NULL (every row must have an identifier).

This constraint guarantees that every record in a table can be distinctly accessed and referenced, preventing data ambiguity.

Example: Consider an example of the Employees Table with three columns.

Employee_ID (PK)	Name	Department
101	Alice	HR
102	Bob	IT
103	Charlie	Finance

How Entity Integrity Works:

Every Employee_ID (PK) must be unique and non-NULL in the Employees table.

✅ Valid: Employee_ID 103 (Charlie) is unique and not empty

❌ Invalid:
- Duplicate ID (101 for two employees)
- NULL value in Employee_ID

2. Referential Integrity Constraint.

Referential Integrity Constraint ensures that relationships between tables remain valid by enforcing that any value in a foreign key (FK) column must match an existing primary key (PK) value in the referenced table or be NULL. This prevents orphaned records (e.g., an order linked to a non-existent customer) and maintains data consistency across related tables.

Key Points:

Ensures only valid relationships exist (e.g., no "ghost" customers for orders).
Use ON DELETE CASCADE to auto-delete child records when a parent is removed.
Blocks insertions/updates that break table relationships.

Example: Consider a relation between the parent (Customer) and child (Order) tables.

Customers (Parent Table)

Customer_ID (PK)	Name
1	Alice
2	Bob

Orders (Child Table)

Order_ID	Customer_ID (FK)	Amount
101	1	99.99
102	2	50.00

How Referential Integrity Works:

Every Customer_ID in the Orders table must exist in the Customers table's Customer_ID column.

✅ Valid: Order 101 links to Customer 1 (Alice)

❌ Invalid: An order with Customer_ID=3 would be rejected (no such customer exists)

3. Domain Integrity Constraint.

Domain Integrity Constraint ensures that all values in a database column adhere to defined rules, such as data types, formats, ranges, or custom conditions. It restricts what data can be entered into a column, preventing invalid or illogical values.

Key Aspects of Domain Integrity

Data Type Enforcement: Columns must contain values of a specific type (e.g., INT, VARCHAR, DATE).
Value Range/Rules: Uses CHECK constraints to limit values (e.g., Age >= 18).
Mandatory Fields: NOT NULL ensures critical columns are never empty.
Default Values: DEFAULT provides fallback values if none are specified.

Example: Employee Table with Domain Constraints

Employee_ID (INT PRIMARY KEY)	Name (VARCHAR(50) NOT NULL)	Age (INT CHECK >=18 AND <=70)	Email (VARCHAR(100) UNIQUE)	Join_Date (DATE DEFAULT CURRENT_DATE)	Salary (DECIMAL(10,2) CHECK >0)
101	Alice	30	alice@example.com	2023-10-05	5000.00

How Domain Integrity Works:

Each column enforces specific data rules:

✅ Valid Example: All values meet their constraints

❌ Violation Examples:

NULL Name: (102, NULL, 25, 'bob@example.com', '2023-10-06', 4500.00)
Age < 18: (103, 'Charlie', 17, 'charlie@example.com', '2023-10-07', 3000.00)
Duplicate Email/Negative Salary: (104, 'Dave', 45, 'alice@example.com', '2023-10-08', -2000.00)

Key Constraints Shown:

NOT NULL - Required fields
CHECK - Value range validation
UNIQUE - No duplicate emails
DEFAULT - Automatic current date

4. Key Constraints (Uniqueness).

Key Constraints (Uniqueness) in database management systems enforce the principle that certain columns or combinations of columns must contain unique values across all rows in a table. These constraints are fundamental for maintaining data integrity by preventing duplicate entries in critical fields.

The two primary types of uniqueness constraints are primary keys and unique keys, each serving distinct but related purposes. A primary key represents the main unique identifier for each record, requiring both uniqueness and non-null values, while a unique key also mandates distinct values but typically allows for one null entry. These constraints are implemented through database declarations that create automatic checks during data insertion or modification, rejecting any operation that would violate the uniqueness rule.

Example: Employee Table Example

CREATE TABLE Employees (
    Employee_ID INT PRIMARY KEY,         -- Primary Key (Unique + NOT NULL)
    Email VARCHAR(100) UNIQUE NOT NULL,  -- Unique Key (No duplicates)
    Passport_Number VARCHAR(20) UNIQUE   -- Unique Key (Allows one NULL)
);

Employee_ID (PK)	Email (Unique)	Passport_Number (Unique)
101	alice@company.com	A12345678
102	bob@company.com	B87654321
103	charlie@company.com	NULL
104	dave@company.com	C55555555

Key Observations:

The Employee_ID column is the primary key - all values are unique and non-null
The Email column has a unique constraint - no duplicates allowed (and NOT NULL enforced)
The Passport_Number column has a unique constraint, but allows one NULL value

Conclusion.

Relational integrity constraints are essential for maintaining accurate, consistent, and reliable databases. By implementing PKs, FKs, CHECK, and UNIQUE constraints, you ensure data quality while improving performance.