Self-Referencing Relationships
Fast recall
Quick revision
Core answer
Understanding of hierarchical data structures and how to model recursive relationships in relational databases
Not making the self-referencing foreign key nullable, which makes it impossible to store root nodes (employees with no manager, top-level categories)
Mention real-world use cases like organizational charts, category trees, and comment threads, and explain how to query hierarchical data with recursive CTEs
Interview-ready guide
Interview answer
A self-referencing relationship connects rows in the same table. In an employee hierarchy, manager_id is a foreign key to employee_id, so one employee may manage many others. If a CEO is represented as having no manager, manager_id must allow NULL; that is a choice about the root representation, not a rule for every recursive relationship. Category trees and threaded comments use the same adjacency-list pattern. Recursive CTEs can traverse several levels, while a normal self-join is enough for one level.
Self-Referencing Relationships
Direct Answer
A self-referencing relationship connects rows of the same entity, such as an employee and that employee's manager. It is commonly implemented with a foreign key such as manager_id that references the same table. The column is nullable when a root row has no parent, but another model can use a different root rule.
Code Example
-- Employee manages Employee (organizational hierarchy)
CREATE TABLE employee (
employee_id BIGSERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
title VARCHAR(50),
manager_id BIGINT, -- Self-referencing FK (nullable)
CONSTRAINT fk_manager FOREIGN KEY (manager_id)
REFERENCES employee(employee_id) ON DELETE SET NULL
);
-- Sample data: CEO (no manager) and two reports
INSERT INTO employee (name, title, manager_id) VALUES
('Alice CEO', 'Chief Executive Officer', NULL), -- Root node
('Bob Manager', 'Engineering Manager', 1),
('Carol Dev', 'Software Developer', 2);
-- Query: Find all direct reports for a manager
SELECT e.name, e.title
FROM employee e
WHERE e.manager_id = 2; -- Bob's reports
-- Recursive CTE: Get entire reporting chain
WITH RECURSIVE org_chart AS (
SELECT employee_id, name, manager_id, 0 AS level
FROM employee WHERE manager_id IS NULL -- Start at root
UNION ALL
SELECT e.employee_id, e.name, e.manager_id, oc.level + 1
FROM employee e
JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT * FROM org_chart ORDER BY level, name;
Common Mistakes & Pitfalls
- Forgetting to make FK nullable: Freshers declare manager_id NOT NULL, making it impossible to insert the first employee (the CEO) because there's no one to reference as manager, causing a chicken-and-egg constraint violation.
- Not handling circular references: Allowing Employee A to manage Employee B while B manages A creates an infinite loop in traversal queries; application code must validate against cycles.
- Ignoring deletion complexity: Deleting an employee with subordinates without ON DELETE SET NULL or cascade logic leaves orphaned employees with invalid manager_id values, breaking referential integrity.
Check your understanding
Answer these without looking back at the article.
- How do you query a self-referencing table to get all subordinates of a manager?
- What is a recursive CTE and how does it work?
Looking for a structured roadmap?
Every module on this page is also part of the full Java backend interview track.