What is referential integrity, and how does a DBMS enforce it?
Interview preparation resource from Gate Smashers.
Referential integrity ensures that a foreign key in a child table either refers to a valid row in the parent table or follows the allowed NULL/default rules. A DBMS enforces it using foreign key constraints and defined referential actions (RESTRICT/NO ACTION, CASCADE, SET NULL, SET DEFAULT) to prevent orphan records and control behavior when referenced parent rows are updated or deleted.
Definition
Referential integrity ensures that a foreign key in a child table either refers to a valid row in the parent table or follows the allowed NULL/default rules.
Enforcement via Foreign Key Constraints
A DBMS enforces referential integrity using foreign key constraints. Consider Students(student_id, name) and Enrollments(student_id, course_id). The student_id in Enrollments is a foreign key that refers to Students.
If an application tries to insert an enrollment for a student that does not exist, the DBMS can reject the operation. This prevents orphan records. The same issue appears when a referenced parent row is updated or deleted; the foreign key can define what the DBMS should do.
Common Referential Actions
The foreign key constraint can specify a referential action to determine how the DBMS handles updates or deletes of the referenced parent row.
- RESTRICT / NO ACTION: prevent the parent update/delete when matching child rows exist.
- CASCADE: automatically apply the related update/delete to child rows.
- SET NULL: keep the child row but clear the foreign key, if NULL is allowed.
- SET DEFAULT: replace the foreign key with its defined default value, if the DBMS/schema supports it.
Purpose
Referential integrity keeps relationships between tables valid, so the database cannot silently contain references to records that do not exist.