Imagine a learning platform like CS Engineering Gyan allowing a student record to be saved without an email address, or an enrollment record referring to a course that does not actually exist in the system. Over time, small inconsistencies like these can accumulate, leading to a database full of unreliable, contradictory, or meaningless data.
Integrity Constraints exist to prevent exactly this kind of problem. They are a set of rules enforced by a database system to ensure that the data stored within it always remains accurate, consistent, and logically valid, regardless of how many applications or users are interacting with it.
In this tutorial, you will learn about the major categories of integrity constraints, including Domain Constraints, Entity Integrity Constraints, Referential Integrity Constraints, and Key Constraints, along with practical examples showing how each one protects the reliability of a database.
Integrity constraints are rules defined within a database that restrict the kind of data that can be inserted, updated, or deleted, ensuring that the database always reflects an accurate and logically consistent representation of the real-world information it is meant to store.
Rather than relying on every application built on top of the database to independently enforce these rules correctly, integrity constraints are defined directly within the database itself, guaranteeing that the rules are enforced consistently, no matter which application or user is interacting with the data.
Integrity constraints are commonly grouped into four major categories, each addressing a different aspect of data reliability within a relational database.
| Constraint Type | Purpose |
|---|---|
| Domain Constraint | Restricts the values an attribute can hold to a specific, valid set or range. |
| Entity Integrity Constraint | Ensures that the primary key of a table is always unique and never null. |
| Referential Integrity Constraint | Ensures that foreign key values always correspond to valid, existing records in another table. |
| Key Constraint | Ensures that specific attributes maintain uniqueness across all rows in a table. |
A domain constraint restricts the values an attribute is allowed to hold, ensuring that every value stored matches an appropriate data type, format, or valid range. This is often the most basic form of integrity constraint, applied at the level of a single attribute.
On the CS Engineering Gyan platform, the Marks attribute in a Students table should logically only accept values within a valid range, such as 0 to 100.
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Marks INT CHECK (Marks >= 0 AND Marks <= 100)
);
With this domain constraint in place, any attempt to insert a value like 150 or -10 into the Marks column would be automatically rejected by the database, since it falls outside the logically valid range defined for that attribute.
The entity integrity constraint states that the primary key of a table must always contain a unique, non-null value for every row. Since the primary key is responsible for uniquely identifying each record, allowing it to be null or duplicated would completely undermine its purpose.
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100)
);
In this CS Engineering Gyan Students table, the entity integrity constraint ensures that StudentID can never be left empty, and that no two students can ever share the same StudentID value, guaranteeing that every student record remains uniquely identifiable.
Without entity integrity, it would become impossible to reliably distinguish between different rows in a table, especially in situations where other attributes, such as names, might coincidentally be identical between two different students.
The referential integrity constraint ensures that a foreign key value in one table always corresponds to an existing, valid primary key value in the referenced table. This constraint is what keeps relationships between multiple tables logically consistent.
Consider the CS Engineering Gyan platform's Courses and Enrollments tables, where Enrollments references Courses through a foreign key.
CREATE TABLE Courses (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(100)
);
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID VARCHAR(10),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
With this referential integrity constraint in place, the database would reject any attempt to insert an enrollment record referencing a CourseID that does not actually exist in the Courses table, preventing broken or meaningless relationships from being created.
Referential integrity also affects what happens when a referenced record is updated or deleted. Databases typically offer several options for handling this situation.
| Action | Effect |
|---|---|
| CASCADE | Automatically updates or deletes related records in the referencing table. |
| SET NULL | Sets the foreign key value to null in related records when the referenced record is removed. |
| RESTRICT | Prevents the update or deletion if related records still reference the original data. |
For example, if a course on the CS Engineering Gyan platform is deleted, a CASCADE rule could automatically remove all related enrollment records, while a RESTRICT rule would instead block the deletion entirely until those enrollments are handled first.
The key constraint ensures that certain attributes, particularly candidate keys, maintain unique values across every row in a table. While closely related to entity integrity, key constraints can apply to any attribute designated as a key, not just the primary key itself.
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100) UNIQUE
);
Here, even though Email is not the primary key, applying a unique constraint ensures that no two students on the CS Engineering Gyan platform can register using the exact same email address, preventing duplicate accounts tied to the same contact information.
Beyond the four major categories, relational databases typically support several additional constraints that work alongside them to further protect data quality.
| Constraint | Description |
|---|---|
| NOT NULL | Ensures a specific attribute cannot be left empty for any row. |
| UNIQUE | Ensures values in a specific attribute remain distinct across all rows. |
| CHECK | Ensures values satisfy a specific logical condition before being accepted. |
| DEFAULT | Automatically assigns a predefined value when no value is explicitly provided. |
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Status VARCHAR(20) DEFAULT 'Active'
);
In this example, every student record on the CS Engineering Gyan platform must have a Name provided, since it cannot be null, while the Status attribute automatically defaults to "Active" whenever a new student is registered without explicitly specifying a different status.
In a well-designed database, these constraints rarely work in isolation. A single table on the CS Engineering Gyan platform might apply domain constraints to validate individual values, entity integrity to guarantee a reliable primary key, key constraints to protect other unique attributes like email, and referential integrity to maintain valid relationships with related tables like Courses and Enrollments.
Together, these layered rules create a database environment where invalid, duplicated, or disconnected data is automatically rejected, regardless of which application or user attempts to insert it, significantly reducing the risk of data quality issues over time.
| Missing Constraint | Potential Consequence |
|---|---|
| No Domain Constraint | Invalid values, such as negative marks, could be stored without any restriction. |
| No Entity Integrity Constraint | Duplicate or null primary keys could make records impossible to reliably identify. |
| No Referential Integrity Constraint | Enrollment records could reference courses that no longer exist, creating broken relationships. |
| No Key Constraint | Multiple accounts could be created using the same email address, causing confusion and potential security issues. |
| Mistake | Correct Practice |
|---|---|
| Relying only on application code to validate data, without database-level constraints. | Define constraints directly within the database to guarantee consistent enforcement. |
| Forgetting to define foreign keys between related tables. | Always establish foreign key relationships to maintain referential integrity. |
| Assuming entity integrity and key constraints are the same thing. | Remember that entity integrity specifically applies to the primary key, while key constraints can apply to other unique attributes as well. |
| Choosing CASCADE without considering its consequences. | Carefully evaluate whether automatic cascading updates or deletions are appropriate for your specific application. |
Integrity constraints form the safeguards that keep a relational database accurate, consistent, and logically sound over time. Domain constraints validate individual attribute values, entity integrity constraints protect the reliability of primary keys, referential integrity constraints maintain valid relationships between related tables, and key constraints protect the uniqueness of important attributes beyond just the primary key.
Together, these constraints ensure that a database powering a platform like CS Engineering Gyan remains trustworthy, whether that means preventing invalid marks from being recorded, stopping duplicate student accounts, or keeping enrollment records properly linked to valid, existing courses. Enforcing these rules at the database level, rather than relying solely on application code, provides a consistent and reliable layer of protection for the data.
With a solid understanding of integrity constraints, you are now ready to explore Functional Dependency, which examines how attributes within a relation depend on one another and lays the groundwork for database normalization.