DataslopeSelf-Referencing Relationships
How to model hierarchies and threads when rows in a table need to point at other rows in the same table.
Sometimes a row relates to another row in the same table. An employee has a manager, who is also an employee. A category has a parent category. A comment may reply to another comment.
This is a self-referencing relationship. The foreign key points back to the table's own primary key.
One table, two roles
The table appears twice in your thinking: once as the child row, and once as the parent row it points to.
manager_id stores another employee's id. The table is not two
different tables; it is one table used in two relationship roles.
The top row often has no parent. In an org chart, the CEO has no manager. In a category tree, the root category has no parent.
The foreign key points back to the same table
A self-reference is still just a foreign key. The referenced table happens to be the same table.
This design gives you a flexible hierarchy without creating separate tables for manager, employee, grandmanager, and so on. Each row stores only its immediate parent.
Quick check
In an employees table, what does manager_id REFERENCES employees(id) mean?
The manager must be stored in a separate managers table.
Each employee row can point to another employee row as its manager.
Every employee must manage themselves.
The table cannot be joined because it references itself.
Querying parent rows with aliases
When a table joins to itself, SQL needs aliases so each role has a
name. Here e means the employee row, and m means the manager row.
The LEFT JOIN matters because Ada has no manager. An inner join would
hide the root row, which is usually not what you want when showing a
whole hierarchy.
Trees, threads, and folders
The same pattern models many structures:
A parent_id column gives each row one parent. Many rows can share the
same parent, which creates a tree.
Guardrails and design limits
A basic self foreign key proves that the parent exists. It does not by itself prevent every possible bad hierarchy.
For example, a foreign key does not automatically prevent cycles such as A parent of B, B parent of C, and C parent of A. It also does not stop a row from pointing to itself unless you add a check.
For this course, the core idea is the table shape: one nullable foreign key points back to the same table's primary key.
Another quick check
Why do self-joins use aliases like employees e and employees m?
Because aliases create temporary copies of the table on disk.
Because the same table is playing two roles in the query, employee and manager.
Because foreign keys require aliases in table definitions.
Because employees is too long for PostgreSQL to parse.
Check your understanding
Which real-world design is a good fit for a self-referencing relationship?
Orders pointing to customers.
Categories where each category may have a parent category.
Students taking courses.
Users having one profile.
Why is manager_id often nullable in an employees table?
Because foreign keys only work when columns are nullable.
Because the top employee in the hierarchy may have no manager.
Because every employee must have two managers.
Because primary keys are nullable too.
What does a basic self-referencing foreign key guarantee?
That the hierarchy can never contain a cycle.
That every row has at least three children.
That any non-null parent id points to an existing row in the same table.
That rows are displayed in tree order automatically.
In the employee-manager query, why use a LEFT JOIN from employees to managers?
To duplicate every employee row twice.
To keep employees who have no manager, such as the top-level CEO.
To prevent the foreign key from being checked.
To require every manager to have the same title.