Loading learning content...
In our study of entity-relationship modeling, we've examined relationships that connect two entity types (binary), three entity types (ternary), and even more. We've also seen that recursive relationships connect an entity type to itself. But there's a special case that deserves focused attention: the unary relationship.
A unary relationship is a recursive relationship of degree one—a relationship involving exactly one entity type. While this might sound like a simple restatement of 'recursive,' understanding unary relationships as a specific category clarifies their properties and optimal use cases.
The term 'unary' emphasizes the structural characteristic: a relationship involving a single entity set. This is contrasted with binary (two entity sets), ternary (three entity sets), and n-ary (n entity sets) relationships.
By the end of this page, you will understand the precise definition of unary relationships, their place in the relationship degree taxonomy, the distinction between unary and general recursive relationships, and when unary modeling is the appropriate choice. You'll develop a clear mental model for categorizing relationships by their structural properties.
Relationship Degree:
The degree of a relationship is the number of distinct entity types participating in it. This is a fundamental classification in ER modeling:
| Degree | Name | Entity Sets | Example |
|---|---|---|---|
| 1 | Unary | E × E | EMPLOYEE supervises EMPLOYEE |
| 2 | Binary | E₁ × E₂ | EMPLOYEE works_in DEPARTMENT |
| 3 | Ternary | E₁ × E₂ × E₃ | SUPPLIER supplies PART to PROJECT |
| n | N-ary | E₁ × E₂ × ... × Eₙ | Multi-participant relationships |
Unary Relationship Definition:
A unary relationship is a relationship of degree 1, where the same entity type participates in both roles of a binary-structured relationship. Formally:
R ⊆ E × E
Where E is a single entity type, and R contains pairs of instances (e₁, e₂) where both e₁ and e₂ belong to E.
A unary relationship connects DIFFERENT instances of the same type (e₁ relates to e₂). This is distinct from a 'self-loop' where an instance relates to ITSELF (e₁ relates to e₁). Most business contexts prohibit self-loops (an employee cannot supervise themselves), but the mathematical definition of unary relationships can include them.
Terminology Clarification:
The terms 'unary,' 'recursive,' and 'self-referential' are often used interchangeably, but there are subtle distinctions:
All three describe the same underlying concept from different perspectives. 'Unary' is the most precise formal term when discussing relationship degree in ER theory.
| Term | Focus | Origin | Usage Context |
|---|---|---|---|
| Unary | Degree of relationship | ER theory | Academic and formal modeling |
| Recursive | Structural pattern | Data modeling | General practice and discussion |
| Self-referential | Table structure | Implementation | Database design and SQL |
| Self-join capable | Query patterns | SQL optimization | Query design and performance |
A common point of confusion: if a unary relationship involves pairs of entities (two roles), why isn't it called binary? The key is understanding what 'degree' counts.
Degree Counts Entity TYPES, Not Roles:
Relationship degree counts the number of distinct entity types participating, not the number of roles or instances:
The SUPERVISES relationship is unary because EMPLOYEE appears once as an entity type in the schema, even though individual instances appear in two different roles within relationship instances.
Why the Distinction Matters:
The degree of a relationship affects:
Schema representation: Unary relationships require role names; binary relationships use entity type names.
Relational mapping: Unary relationships map to self-referencing FKs in a single table; binary relationships map to FKs between distinct tables.
Query patterns: Unary relationships require self-joins; binary relationships use standard joins.
Constraint enforcement: Unary relationships may need special constraints (no cycles, no self-reference); binary relationships rarely face these issues.
When determining relationship degree, count each entity type ONCE, regardless of how many times it appears. If you see the same entity type appearing multiple times in a relationship schema, each appearance counts as ONE, using the same type. The result is the relationship degree.
Unary relationships create rich internal structures within an entity set. The type of structure depends on the relationship's cardinality:
1:1 Unary Relationships - Paired Structures:
When each instance can be related to at most one other instance in each role, we get paired or chain structures:
1:1 Recursive: MARRIED_TO on PERSON Paired Structure:┌────────┐ ┌────────┐│ Alice │─────────│ Bob │└────────┘ └────────┘ ┌────────┐ ┌────────┐│ Carol │─────────│ Dave │└────────┘ └────────┘ ┌────────┐│ Eve │ (unpaired)└────────┘ Chain Structure (1:1 non-symmetric, like NEXT_VERSION):┌────────┐ ┌────────┐ ┌────────┐ ┌────────┐│ v1.0 │───►│ v1.1 │───►│ v1.2 │───►│ v2.0 │└────────┘ └────────┘ └────────┘ └────────┘ no prev ▲ ▲ no next1:N Unary Relationships - Hierarchical Trees:
When one role can relate to many instances in the other role, but not vice versa, we get tree structures:
1:N Recursive: SUPERVISES on EMPLOYEE Hierarchical Tree Structure: ┌─────────┐ │ CEO │ (root - no supervisor) └────┬────┘ ┌─────────────┼─────────────┐ ▼ ▼ ▼ ┌─────────┐ ┌─────────┐ ┌─────────┐ │ VP Eng │ │VP Sales │ │ VP Ops │ └────┬────┘ └────┬────┘ └────┬────┘ ┌─┴──┐ │ │ ▼ ▼ ▼ ▼ ┌────┐┌────┐ ┌────┐ ┌────┐ │Mgr1││Mgr2│ │Mgr3│ │Mgr4│ └──┬─┘└──┬─┘ └──┬─┘ └────┘ (leaf - no subordinates) │ │ │ ▼ ▼ ▼ ┌───┐ ┌───┐ ┌───┐ │Dev│ │Dev│ │Rep│ └───┘ └───┘ └───┘ (leaves - no subordinates) Properties:- Exactly one root (no supervisor)- Each non-root has exactly one parent- Leaves have no children- No cycles allowedM:N Unary Relationships - Graph Networks:
When each role can relate to many instances, we get graph structures:
M:N Recursive: PREREQUISITE_FOR on COURSE Directed Acyclic Graph (DAG):┌────────┐ ┌────────┐│Math 101│────►│Math 201│────┐└────────┘ └────────┘ │ │ │ ▼ ▼┌────────┐ ┌────────┐ ┌────────┐│Phys 101│────►│Phys 201│ │Engr 301│└────────┘ └────────┘ └────────┘ │ ▲ └──────────┘ - Courses can have MULTIPLE prerequisites- Courses can BE prerequisites for MULTIPLE other courses- Usually acyclic (can't require itself transitively) M:N Recursive with Cycles: FOLLOWS on USER General Graph:┌────────┐◄───────┌────────┐│ Alice │ │ Bob │◄───┐└────────┘────────►└────────┘ │ │ │ │ │ ┌────────────┘ │ ▼ ▼ │┌────────┐ ┌────────┘│ Carol │──────────────│└────────┘ - Users can follow many users- Users can be followed by many users- Cycles allowed (mutual following)The structure created by a unary relationship directly impacts implementation strategies. Trees can use nested set or path enumeration models. DAGs need careful cycle prevention. General graphs require junction tables. Identifying the structure type early guides design decisions.
Representing unary relationships in ER diagrams requires special notation since the same entity type must connect to itself. Different notations handle this differently.
Chen Notation:
Draw two lines from the entity rectangle to the relationship diamond, with role names labeling each line:
Unary 1:N: SUPERVISES on EMPLOYEE ┌─────────────────────┐ │ EMPLOYEE │ │ │ └──────────┬──────────┘ ╱ │ ╲ ╱ │ ╲ (supervisor) │ (subordinate) ╲ │ ╱ ╲ │ ╱ ┌──────────┴──────────┐ │◇ SUPERVISES ◇ │ └─────────────────────┘ 1:N With cardinality indicators:- "1" near supervisor role (each subordinate has at most 1 supervisor)- "N" near subordinate role (each supervisor can have many subordinates) Alternative: Loop representation ┌──────────────────────┐ │ EMPLOYEE │─────────────────┐ └──────────────────────┘ │ ▲ │ │ (supervisor) │ │ │ ┌──────────────────┘ │ │ │ ◇ SUPERVISES ◇ │ │ │ │ (subordinate) │ └──────────┘Crow's Foot (IE) Notation:
Draw a relationship line that loops from the entity back to itself, with cardinality markers at each end:
Crow's Foot: SUPERVISES on EMPLOYEE (1:N) ┌─────────────────────────────────────────────────────┐│ ││ manages ○──────────┐ ││ │ ││ ┌───────────────────────┤ ││ │ EMPLOYEE │ ││ ├───────────────────────┤ ││ │ PK employee_id │ ││ │ name │ ││ │ FK supervisor_id ◄───┼────┘ ││ └───────────────────────┘ ││ │ ││ │ ││ managed by ├─────────►< ││ (many) ││ │└─────────────────────────────────────────────────────┘ Cardinality markers:○ = Zero or one (optional)─ = One (exactly one) >──< = Many (crow's foot) The loop shows:- Employee (as supervisor) --manages-- zero or more Employees- Employee (as subordinate) --managed by-- zero or one EmployeeThe looping relationship line can make diagrams visually complex. Many designers place unary relationships prominently with clear role labels. Some use color coding or line thickness to distinguish self-referencing relationships from regular foreign keys.
Unary relationships map to relational schemas using self-referencing foreign keys. The specific pattern depends on cardinality:
1:N Unary → Self-Referencing FK in Entity Table:
1234567891011121314151617181920212223242526272829
-- 1:N Unary: SUPERVISES on EMPLOYEE-- The "N" side (subordinate) gets the FK CREATE TABLE Employee ( employee_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, hire_date DATE NOT NULL, salary DECIMAL(10,2), -- Self-referencing FK: points to supervisor supervisor_id INT, CONSTRAINT fk_supervisor FOREIGN KEY (supervisor_id) REFERENCES Employee(employee_id) ON DELETE SET NULL); -- CEO has NULL supervisor_id-- All others point to their supervisor's employee_id -- Query: Find all direct reports of employee #1SELECT * FROM Employee WHERE supervisor_id = 1; -- Query: Find the supervisor of employee #5SELECT e.* FROM Employee eJOIN Employee subordinate ON subordinate.supervisor_id = e.employee_idWHERE subordinate.employee_id = 5;M:N Unary → Junction Table:
Just like M:N relationships between different entity types, M:N unary relationships require a junction table:
12345678910111213141516171819202122232425262728293031323334353637383940414243444546
-- M:N Unary: PREREQUISITE_FOR on COURSE-- Junction table with two FKs to the same table CREATE TABLE Course ( course_id INT PRIMARY KEY, course_code VARCHAR(20) NOT NULL UNIQUE, title VARCHAR(200) NOT NULL, credits INT NOT NULL); -- Junction table for the M:N relationshipCREATE TABLE Course_Prerequisite ( -- Both FKs reference Course table prerequisite_course_id INT NOT NULL, dependent_course_id INT NOT NULL, -- Composite PK prevents duplicate relationships PRIMARY KEY (prerequisite_course_id, dependent_course_id), -- Both FKs reference the SAME table CONSTRAINT fk_prerequisite FOREIGN KEY (prerequisite_course_id) REFERENCES Course(course_id) ON DELETE CASCADE, CONSTRAINT fk_dependent FOREIGN KEY (dependent_course_id) REFERENCES Course(course_id) ON DELETE CASCADE, -- Prevent a course from being its own prerequisite CONSTRAINT chk_no_self_prereq CHECK (prerequisite_course_id != dependent_course_id)); -- Query: Find all prerequisites for course #10SELECT c.* FROM Course cJOIN Course_Prerequisite cp ON c.course_id = cp.prerequisite_course_idWHERE cp.dependent_course_id = 10; -- Query: Find all courses that depend on course #5SELECT c.*FROM Course cJOIN Course_Prerequisite cp ON c.course_id = cp.dependent_course_idWHERE cp.prerequisite_course_id = 5;In M:N unary relationships, a CHECK constraint should typically prevent an entity from relating to itself (unless the domain explicitly allows it). The constraint 'CHECK (column1 != column2)' prevents such self-references at the database level.
Querying unary relationships requires self-joins—joining a table to itself. This is a distinctive query pattern that distinguishes unary relationship queries from standard joins.
The Self-Join Pattern:
12345678910111213141516171819202122232425262728293031323334353637383940
-- Basic Self-Join: Find employees with their supervisorsSELECT e.employee_id, e.name AS employee_name, s.name AS supervisor_nameFROM Employee eLEFT JOIN Employee s ON e.supervisor_id = s.employee_id; /*Result:employee_id | employee_name | supervisor_name------------|---------------|----------------1 | Alice (CEO) | NULL2 | Bob | Alice3 | Carol | Alice4 | Dave | Bob5 | Eve | Bob6 | Frank | Carol*/ -- The same table (Employee) appears twice with different aliases!-- 'e' represents employee in SUBORDINATE role-- 's' represents employee in SUPERVISOR role -- Find employees whose supervisor earns more than 100kSELECT e.name, e.salary, s.name AS supervisor, s.salary AS sup_salaryFROM Employee eJOIN Employee s ON e.supervisor_id = s.employee_idWHERE s.salary > 100000; -- Count direct reports per supervisorSELECT s.employee_id, s.name AS supervisor, COUNT(e.employee_id) AS direct_report_countFROM Employee sLEFT JOIN Employee e ON e.supervisor_id = s.employee_idGROUP BY s.employee_id, s.nameHAVING COUNT(e.employee_id) > 0ORDER BY direct_report_count DESC;Multi-Level Traversal:
Traversing multiple levels of a hierarchy requires multiple self-joins or recursive queries:
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748
-- Three-level hierarchy: Employee → Manager → VPSELECT e.name AS employee, m.name AS manager, vp.name AS vpFROM Employee eLEFT JOIN Employee m ON e.supervisor_id = m.employee_idLEFT JOIN Employee vp ON m.supervisor_id = vp.employee_id; -- This approach doesn't scale well for deep/variable-depth hierarchies -- Recursive CTE for arbitrary depth (standard SQL)WITH RECURSIVE OrgHierarchy AS ( -- Base case: start from the CEO (no supervisor) SELECT employee_id, name, supervisor_id, 0 AS level, name AS path FROM Employee WHERE supervisor_id IS NULL UNION ALL -- Recursive case: join to find subordinates SELECT e.employee_id, e.name, e.supervisor_id, h.level + 1, h.path || ' -> ' || e.name FROM Employee e JOIN OrgHierarchy h ON e.supervisor_id = h.employee_id)SELECT * FROM OrgHierarchy ORDER BY level, name; /*Result:employee_id | name | level | path------------|---------|-------|---------------------------1 | Alice | 0 | Alice2 | Bob | 1 | Alice -> Bob3 | Carol | 1 | Alice -> Carol4 | Dave | 2 | Alice -> Bob -> Dave5 | Eve | 2 | Alice -> Bob -> Eve6 | Frank | 2 | Alice -> Carol -> Frank*/Self-joins require table aliases to distinguish the two 'copies' of the table. Use meaningful aliases that reflect roles: 'e' for employee, 's' for supervisor—not generic 't1' and 't2'. Clear aliases make queries readable and maintainable.
Unary relationships are appropriate when entities of the same type genuinely relate to each other. Recognizing when this pattern applies—versus when it doesn't—is a key modeling skill.
Use Unary Relationships When:
Consider Separate Entity Types When:
Ask: 'Do both participants in this relationship belong to the same real-world category with the same essential properties?' If YES (employees supervising employees), use unary. If the answer is nuanced (instructors vs. students who happen to both be people), consider separate types.
Unary relationships are the formal name for degree-1 relationships where an entity type relates to itself. Let's consolidate the key points:
What's Next:
The next page examines Hierarchies—the most common structural pattern created by 1:N unary relationships. You'll learn about tree properties, traversal algorithms, specialized implementation models (nested sets, path enumeration), and how to handle hierarchical data efficiently in relational databases.
You now understand unary relationships as degree-1 relationships in the ER taxonomy, their structural implications, implementation patterns, and appropriate use cases. This formal understanding provides the foundation for working with the specific hierarchical patterns explored next.