Integrity constraints are rules that keep database data valid, consistent and meaningful. Domain integrity controls what values are allowed in a column, key constraints protect uniqueness, entity integrity makes sure every row can be identified through a non-NULL primary key, and referential integrity keeps foreign-key references valid. In this chapter, you will learn not only the definitions, but also exactly what happens during INSERT, UPDATE and DELETE operations, how NULL behaves, how foreign keys work, and when CASCADE, SET NULL, RESTRICT and related actions are useful.
What Are Integrity Constraints in DBMS?
Integrity constraints are conditions that valid database data must satisfy. They prevent impossible, duplicate, unidentified or wrongly linked records from entering the database.
Imagine a college database that accepts anything without rules. A student could have semester 99, two students could have the same student ID, a row could exist without any primary-key value, or a fee record could point to a student who does not exist. The database would still contain data, but the data would no longer be trustworthy.
That is why the relational model uses constraints. They act like guards around the data.
Shortcut #1: D-K-E-R
D = Domain · K = Key · E = Entity · R = ReferentialThink of four questions: Allowed? Unique? Identifiable? Valid reference?
If relation, tuple, attribute, domain, keys or foreign keys are still new, first revise Relational Model in DBMS: Relation, Tuple, Attribute & Domain Explained. This chapter builds directly on that foundation.
Interactive Integrity Constraint Violation Lab
Use the buttons below. Each one creates a different invalid situation in a small college database.
Constraint Tester
Parent table: DEPARTMENT. Child table: STUDENT.
| student_id (PK) | name | semester | department_id (FK) |
|---|---|---|---|
| S01 | Asha | 2 | D01 |
| S02 | Ravi | 2 | D02 |
This visual lab is a teaching model. A real DBMS checks constraints using the actual schema definition, SQL rules and transaction behavior.
The Four Main Integrity Constraints
Shortcut #2: Value → Unique → Identify → Reference
Domain checks the value. Key checks uniqueness. Entity integrity guarantees identification. Referential integrity protects references.
Domain Integrity in DBMS
A domain defines the meaningful values that an attribute is allowed to take. Domain integrity makes sure stored values stay inside that permitted set.
Allowed Semester Domain
STUDENT.semester
Valid: 1, 2, 5, 8
Invalid for this design: 0, 9, "final", 99
How is domain integrity enforced in SQL?
Depending on the database system and design, domain rules can be enforced using datatypes, NOT NULL, CHECK constraints, generated rules, reference tables and application-level validation.
CREATE TABLE Student (
student_id VARCHAR(20) PRIMARY KEY,
name VARCHAR(100) NOT NULL,
semester INT NOT NULL,
email VARCHAR(150),
CHECK (semester BETWEEN 1 AND 8)
);Here, INT prevents text like “hello” from being treated as a normal integer semester value, while the CHECK constraint restricts the accepted range to 1 through 8.
Datatype alone is not a complete domain rule
If semester is an integer, the datatype might still allow values like 500 unless an additional rule blocks them. So the real business domain is often narrower than the technical datatype.
Shortcut #3: Datatype says “what kind”; domain says “which values”
INTEGER ≠ valid semester rangeMore domain-integrity examples
Key Constraint in DBMS
A key constraint protects uniqueness. If an attribute set is a candidate key, no two distinct tuples are allowed to have the same key value combination.
Suppose both student_id and email are unique for every student. Then both can be candidate keys. If student_id is selected as the primary key, email can still be protected as an alternate key using a UNIQUE constraint.
CREATE TABLE Student (
student_id VARCHAR(20) PRIMARY KEY,
email VARCHAR(150) UNIQUE,
name VARCHAR(100) NOT NULL
);Duplicate primary key example
Allowed
INSERT INTO Student
VALUES ('S01', 'asha@example.com', 'Asha');S01 does not exist yet, so the primary key remains unique.
Rejected
INSERT INTO Student
VALUES ('S01', 'ravi@example.com', 'Ravi');If S01 already exists, the insert violates the primary-key uniqueness rule.
Composite candidate keys
A key does not have to contain only one attribute. In ENROLLMENT, (student_id, course_id) may be a composite candidate key if one student can have at most one active enrollment record for one course.
The advanced key chapter explains this hierarchy in more depth: Keys, Mapping Constraints, Generalization & Aggregation in ER Model.
Entity Integrity in DBMS
Entity integrity says that the primary key of a relation cannot contain NULL. Every tuple must have a definite identity.
Valid Student
student_id: S01
name: Asha
The tuple can be identified clearly.
Invalid Student
student_id: NULL
name: Ravi
There is no valid primary-key identity for this tuple.
Why is NULL not allowed in the primary key?
A primary key exists to identify one exact tuple. NULL represents absence of a normal value, so a NULL primary key would fail at the basic job of identification.
What if the primary key is composite?
If the primary key is made from multiple attributes, each component is part of the tuple's identity. In SQL primary-key constraints, the primary-key columns are non-NULL.
CREATE TABLE Enrollment (
student_id VARCHAR(20),
course_id VARCHAR(20),
grade VARCHAR(5),
PRIMARY KEY (student_id, course_id)
);Here, both student_id and course_id are required as parts of the composite primary key.
Shortcut #4: Entity Integrity = “No nameless primary identity”
Primary Key → UNIQUE + NOT NULL identityReferential Integrity in DBMS
Referential integrity protects relationships between relations.
Suppose we have a parent relation DEPARTMENT and a child relation STUDENT.
DEPARTMENT
| department_id (PK) | name |
|---|---|
| D01 | Computer Science |
| D02 | Electronics |
STUDENT
| student_id | name | department_id (FK) |
|---|---|---|
| S01 | Asha | D01 |
| S02 | Ravi | D02 |
The foreign key STUDENT.department_id refers to a key in DEPARTMENT. A student cannot normally point to department D99 if D99 does not exist in the referenced parent key.
Basic referential-integrity rule
Shortcut #5: Foreign key has two safe states
FK = matching parent key OR permitted NULLWhether NULL is permitted depends on the schema. If the relationship is mandatory, the foreign-key column can also be declared NOT NULL.
Invalid child insert
INSERT INTO Student
(student_id, name, department_id)
VALUES ('S03', 'Neha', 'D99');If D99 does not exist in DEPARTMENT and a foreign-key constraint is active, the DBMS rejects the insert.
Valid NULL foreign key?
If department_id is allowed to be NULL, a student may temporarily exist without a department assignment:
INSERT INTO Student
(student_id, name, department_id)
VALUES ('S04', 'Meera', NULL);This does not violate referential integrity because NULL is not pretending to reference a missing parent. If the relationship must be compulsory, add NOT NULL as well.
What Happens When a Referenced Parent Row Is Updated or Deleted?
Suppose D01 is referenced by 300 students. What should happen if someone tries to delete D01 from DEPARTMENT?
The database design must define the intended referential action.
CASCADE
Related child references are automatically updated or deleted along with the parent action.
RESTRICT / NO ACTION
The parent change is rejected when dependent child references would become invalid. Exact timing semantics can vary by DBMS.
SET NULL
The child foreign key becomes NULL after the parent is deleted or changed, if that foreign key permits NULL.
SET DEFAULT
The child foreign key is set to its defined default value, where the DBMS supports the action and the result remains valid.
Example foreign-key definition
CREATE TABLE Student (
student_id VARCHAR(20) PRIMARY KEY,
name VARCHAR(100) NOT NULL,
department_id VARCHAR(10),
CONSTRAINT fk_student_department
FOREIGN KEY (department_id)
REFERENCES Department(department_id)
ON UPDATE CASCADE
ON DELETE SET NULL
);In this design, changing a referenced department ID can propagate to child rows, while deleting a department clears the student reference instead of deleting the student.
Should you always use CASCADE?
No. CASCADE is useful only when automatic propagation matches the real business meaning. For important master data, silently deleting hundreds of child rows can be dangerous. Choose the referential action based on the relationship, not convenience.
Shortcut #6: Parent change asks “Block, Follow, Clear or Default?”
RESTRICT · CASCADE · SET NULL · SET DEFAULTNULL, NOT NULL and Integrity Constraints
NULL is one of the most misunderstood parts of database constraints.
SELECT *
FROM Student
WHERE department_id IS NULL;Complete SQL Example With Domain, Key, Entity and Referential Integrity
CREATE TABLE Department (
department_id VARCHAR(10) PRIMARY KEY,
department_name VARCHAR(100) NOT NULL UNIQUE
);
CREATE TABLE Student (
student_id VARCHAR(20) PRIMARY KEY,
name VARCHAR(100) NOT NULL,
semester INT NOT NULL,
email VARCHAR(150) UNIQUE,
department_id VARCHAR(10),
CHECK (semester BETWEEN 1 AND 8),
CONSTRAINT fk_student_department
FOREIGN KEY (department_id)
REFERENCES Department(department_id)
ON UPDATE CASCADE
ON DELETE SET NULL
);What each line protects
How Constraints Are Checked During INSERT, UPDATE and DELETE
| Operation | Typical checks | Possible violation |
|---|---|---|
| INSERT child row | Domain, NOT NULL, key uniqueness, foreign-key reference | Invalid semester, duplicate key, NULL primary key, missing parent |
| UPDATE normal column | Domain and business rules | Changing marks from 80 to 150 |
| UPDATE key | Uniqueness + possible referenced children | Duplicate new key or orphaned foreign keys |
| UPDATE foreign key | New reference must remain valid | Changing D01 to missing D99 |
| DELETE parent row | Referential action | Would leave child rows pointing to nothing |
| DELETE child row | Usually simpler for parent reference | Other business rules may still apply |
This is why constraints are not just static definitions. They actively control which database states are allowed after data-changing operations.
Database Constraints vs Application Validation
Good applications usually validate data before sending it to the database, but important integrity rules should not depend only on the user interface.
Application Validation
Gives fast, friendly feedback such as “Semester must be between 1 and 8.” It improves user experience.
Database Constraint
Protects the data even if another application, script, import tool or developer sends invalid data directly.
For important invariants, using both is usually stronger: the application explains the problem early, and the database remains the final guard.
Integrity Constraints and the Relational Model
The relational model is not only about storing rows and columns. A valid relation state should also respect the schema's constraints.
Think of it like this:
This is the natural next step after the Relational Model chapter. Relation, tuple, attribute and domain tell you what the structure is; integrity constraints tell you which data states are allowed inside that structure.
College Database Example: All Four Constraints Together
Suppose a college has DEPARTMENT and STUDENT relations.
Domain rule
Semester must be between 1 and 8.
Key rule
Every student_id must be unique. Email is also unique if the college uses one account per student.
Entity-integrity rule
student_id cannot be NULL.
Referential-integrity rule
If department_id is present, it must refer to an existing department.
Now test four inserts
| Insert | Result | Reason |
|---|---|---|
| S10, Asha, semester 3, D01 | Accept | All constraints are satisfied. |
| S11, Ravi, semester 15, D01 | Reject | Domain/check violation. |
| S10, Neha, semester 2, D02 | Reject | Duplicate primary key. |
| S12, Meera, semester 1, D99 | Reject | Missing referenced department. |
This is the chapter in one picture: every invalid row fails for a different reason.
Do Not Confuse These Integrity Rules
How to Write Integrity Constraints in Semester Exams
Definition + one example
Define the asked constraint directly. Example: “Entity integrity requires the primary key to be non-NULL so every tuple can be uniquely identified.”
Explain four constraints
Write Domain, Key, Entity and Referential integrity with one short college example for each.
Add SQL + violation cases
Explain all four constraints, show parent/child relations, add primary and foreign keys, discuss NULL and referential actions, then include SQL examples.
For exam presentation, use How to Write High-Scoring Answers in University Semester Exams. For unit prioritisation, use the previous-year-paper method.
Practice Questions With Answers
1. What is an integrity constraint in DBMS?
An integrity constraint is a rule that valid database data must satisfy. It helps prevent invalid, duplicate, unidentified or incorrectly referenced records.
2. What is domain integrity?
Domain integrity requires an attribute value to come from the allowed domain defined for that attribute.
3. What is a key constraint?
A key constraint requires candidate-key values to uniquely identify tuples, so two distinct tuples cannot share the same candidate-key value combination.
4. What is entity integrity?
Entity integrity requires the primary key to be non-NULL so every tuple has a valid identity.
5. What is referential integrity?
Referential integrity requires a foreign-key value to match a permitted referenced key value in the parent relation, unless the foreign key is allowed to be NULL.
6. Can a foreign key be NULL?
Yes, if the schema permits an optional relationship. If the relationship is mandatory, the foreign-key column can also be declared NOT NULL.
7. Can a primary key be NULL?
No. A NULL primary key would violate entity integrity because the tuple would not have a valid primary-key identity.
8. What is ON DELETE CASCADE?
It is a referential action that automatically deletes dependent child rows when the referenced parent row is deleted. It should be used only when that behavior matches the data model.
9. What is the difference between SET NULL and RESTRICT?
SET NULL removes the child reference by making the foreign key NULL, while RESTRICT blocks the parent change when dependent references exist.
10. Why are database constraints needed if an application already validates input?
Other applications, scripts, imports or direct database access can bypass one user interface. Database constraints protect the data at the database layer itself.
Integrity Constraints One-Minute Revision Sheet
Frequently Asked Questions
What are the four main integrity constraints in DBMS?
The four commonly taught integrity constraints are domain integrity, key constraints, entity integrity and referential integrity.
What is the difference between entity integrity and referential integrity?
Entity integrity protects the primary-key identity of a tuple by disallowing NULL primary keys. Referential integrity protects relationships by requiring foreign-key references to remain valid.
What is the difference between domain constraint and key constraint?
A domain constraint checks whether a value is allowed for an attribute. A key constraint checks whether identifying values remain unique.
Can a foreign key contain NULL?
Yes, when the schema permits the relationship to be optional. A NOT NULL constraint can be added when every child row must have a parent reference.
What happens when a parent row referenced by a foreign key is deleted?
The result depends on the referential action defined by the schema, such as blocking the delete, cascading it to child rows, setting child foreign keys to NULL, or another supported action.
What should I study after integrity constraints?
Study relational algebra, SQL constraints in depth, functional dependencies and normalization. These topics build directly on valid relational design.
What Should You Learn After Integrity Constraints?
Next: Relational Algebra
You now know what a valid relational database state looks like. The next step is to learn how relations are transformed and queried using Relational Algebra: selection, projection, union, set difference, Cartesian product, rename and joins.
After relational algebra, continue with SQL, functional dependencies and normalization. For video revision after understanding the written chapter, you can use the DBMS YouTube study guide as a secondary resource.
