What is Referential Integrity?
Understand referential integrity in databases: how foreign keys enforce valid relationships and prevent orphaned records.
Expected Interview Answer
Referential integrity is a database rule ensuring that a foreign key value in one table always matches an existing primary key value in the referenced table, preventing orphaned or dangling references.
When two tables are related through a foreign key, referential integrity guarantees that you cannot insert a child row pointing to a parent that does not exist, and by default you cannot delete a parent row that still has dependent child rows. Databases enforce this automatically once foreign key constraints are declared, rejecting operations that would break the link. Options like ON DELETE CASCADE, SET NULL, or RESTRICT let developers control what happens to child rows when a parent is removed. Without referential integrity, applications risk orphaned records that silently corrupt reports and break joins.
- Prevents orphaned child records
- Guarantees valid foreign key relationships
- Protects join and report accuracy
- Enforces consistency automatically at the database level
AI Mentor Explanation
A league would never let a match record reference a team ID that does not exist in the official team registry — the system rejects any scorecard pointing to a phantom team. Referential integrity is that exact safeguard: a foreign key in the match table must always point to a real, existing row in the team table. If someone tries to delete a team that still has scheduled matches referencing it, the system blocks the deletion or requires the matches to be reassigned first.
Step-by-Step Explanation
Step 1
Define primary keys
Each parent table must have a unique primary key that child rows can reference.
Step 2
Declare foreign keys
Add a foreign key constraint on the child table column pointing to the parent’s primary key.
Step 3
Choose a delete rule
Decide the behavior for parent deletion: RESTRICT, CASCADE, or SET NULL for dependent child rows.
Step 4
Database enforces automatically
Any insert or delete that would break the reference is rejected unless it satisfies the constraint rules.
What Interviewer Expects
- Correct definition involving foreign key and primary key matching
- Awareness of CASCADE, SET NULL, and RESTRICT options
- Understanding of what happens on invalid inserts or deletes
- A concrete example of an orphaned record scenario
Common Mistakes
- Confusing referential integrity with entity integrity (primary key uniqueness)
- Forgetting that deletes can be blocked or cascaded depending on configuration
- Assuming referential integrity is enforced by application code, not the database
- Not knowing what ON DELETE CASCADE does
Best Answer (HR Friendly)
“Referential integrity ensures that relationships between tables stay valid — a foreign key always points to a row that actually exists in the related table. This prevents orphaned records, like an order referencing a customer that was deleted, and databases enforce it automatically through foreign key constraints with rules like cascade delete or restrict.”
Code Example
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE RESTRICT
);
-- Rejected: no such customer_id in customers table
INSERT INTO orders (order_id, customer_id, amount) VALUES (1, 999, 50.00);Follow-up Questions
- What is the difference between ON DELETE CASCADE and ON DELETE SET NULL?
- How does referential integrity differ from entity integrity?
- Can referential integrity be violated in a NoSQL database?
- What happens if you try to insert a foreign key value with no matching parent row?
MCQ Practice
1. What does referential integrity guarantee?
Referential integrity ensures foreign key values always correspond to real rows in the parent table.
2. Which foreign key option automatically deletes child rows when the parent is deleted?
ON DELETE CASCADE automatically removes dependent child rows when the referenced parent row is deleted.
3. What happens by default when inserting a row with a foreign key value that has no matching parent row?
A foreign key constraint rejects inserts that reference a non-existent parent row, enforcing referential integrity.
Flash Cards
What is referential integrity? — A rule ensuring a foreign key always matches an existing primary key in the referenced table.
What does ON DELETE CASCADE do? — Automatically deletes dependent child rows when the referenced parent row is deleted.
What does ON DELETE RESTRICT do? — Blocks deletion of a parent row if dependent child rows still reference it.
What problem does referential integrity prevent? — Orphaned records — child rows pointing to a parent that no longer exists.