FullStackPrep.Dev
Crack Interviews Fast
🌞 Light

Detect and Fix Orphaned Records in a Relational Database

💡 Concept Name

Orphaned Records in SQL – Rows in a child table that reference a non-existent parent record, violating referential integrity in relational databases.

📘 Quick Intro

Orphaned records are rows in a table that reference missing primary keys from another table. They often occur due to missing foreign key constraints or incorrect deletions. Detecting and fixing them helps maintain data integrity.

🧠 Analogy / Short Story

Imagine a list of students enrolled in courses. If the course catalog is deleted but students still reference those missing courses, it’s like listing people who are attending a party that no longer exists—those students are now “orphans” without context.

🔧 Technical Explanation

  • 🔍 Use LEFT JOIN or NOT EXISTS to detect orphaned child rows.
  • 🛑 Orphaned records arise when foreign key constraints are missing or violated.
  • 🧹 Fix by deleting, updating, or inserting appropriate parent records.
  • 🛡️ Prevent using FOREIGN KEY constraints with ON DELETE CASCADE or ON DELETE SET NULL.
  • 🧪 Routine integrity checks should be part of maintenance.

💻 Real Code Example

-- Detect orphaned records: Find orders with no existing customer
SELECT o.*
FROM Orders o
LEFT JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE c.CustomerID IS NULL;

-- Alternative using NOT EXISTS
SELECT *
FROM Orders o
WHERE NOT EXISTS (
  SELECT 1 FROM Customers c WHERE c.CustomerID = o.CustomerID
);

-- Fix: Delete orphaned orders
DELETE FROM Orders
WHERE CustomerID NOT IN (
  SELECT CustomerID FROM Customers
);

-- Prevent future issues: Add foreign key constraint
ALTER TABLE Orders
ADD CONSTRAINT FK_Customer
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID);

🎯 Purpose & Use Case

  • ✅ Maintain referential integrity between related tables.
  • ✅ Clean up leftover data from faulty deletions or migrations.
  • ✅ Prevent misleading reports and broken application logic.
  • ✅ Improve database performance and consistency.

❓ Interview Q&A

Q1: What is an orphaned record?
A: A row in a child table that references a non-existent parent record.

Q2: How do orphaned records happen?
A: They occur when parent rows are deleted without enforcing foreign key constraints.

Q3: How can you detect orphaned records?
A: Use LEFT JOIN ... WHERE parent IS NULL or NOT EXISTS queries.

Q4: How do you fix orphaned records?
A: By deleting, updating, or restoring valid parent records.

Q5: What constraint helps prevent orphans?
A: A FOREIGN KEY with appropriate ON DELETE behavior.

Q6: What is ON DELETE CASCADE?
A: It automatically deletes child records when the parent is removed.

Q7: Is it always safe to delete orphaned records?
A: Not always—review business logic before mass deletions.

Q8: What if you can’t use foreign keys?
A: Implement periodic checks and triggers for integrity.

Q9: How do orphan records affect performance?
A: They waste space and can break joins or logic depending on clean data.

Q10: Can foreign keys be added to existing tables?
A: Yes, but ensure existing data is clean first.

📝 MCQs

Q1. What is an orphaned record in SQL?

  • Unindexed row
  • Primary key violation
  • Child row with no parent
  • Extra join column

Q2. Which SQL clause helps detect orphans?

  • UNION
  • GROUP BY
  • LEFT JOIN
  • DISTINCT

Q3. Which constraint helps avoid orphaned records?

  • CHECK
  • UNIQUE
  • INDEX
  • FOREIGN KEY

Q4. Which option deletes child when parent is deleted?

  • ON INSERT
  • ON DELETE SET DEFAULT
  • ON DELETE CASCADE
  • ON UPDATE

Q5. What happens if a foreign key is missing?

  • Nothing
  • Orphan records can be created
  • Index is ignored
  • Query fails

Q6. Which query finds orphans using NOT EXISTS?

  • JOIN parent ON TRUE
  • SELECT with GROUP BY
  • Child WHERE NOT EXISTS parent
  • UPDATE JOIN

Q7. How do you delete orphan records?

  • DROP TABLE
  • ALTER CONSTRAINT
  • DELETE WHERE parent not in child
  • ROLLBACK

Q8. Can orphans affect app behavior?

  • No
  • Yes, they cause logical errors
  • Only in NoSQL
  • Only in indexes

Q9. Which database feature enforces integrity?

  • Stored procedure
  • Triggers only
  • Relational constraints
  • Text columns

Q10. Is ON DELETE SET NULL useful?

  • No
  • Yes, for optional relationships
  • Only in views
  • Used for logging

💡 Bonus Insight

Always perform a backup before modifying or deleting orphaned records. Tools like SQL Server Profiler, PostgreSQL EXPLAIN, or MySQL Workbench can help trace where and how orphans are created in production.

📄 PDF Download

Need a handy summary for your notes? Download this topic as a PDF!

💬 Feedback
🚀 Start Learning
Share:

Tags: