Can a foreign key refer to a primary key in the same table?

sql

database-normalization

self-referencing-keys

foreign-key

byAnton Shumikhin·Dec 22, 2024

Absolutely, foreign keys (FKs) can indeed reference primary keys (PKs) in the same table. This forms a self-referencing relationship. To clarify with an example, let's consider an employee hierarchy:

CREATE TABLE Employees (
  ID INT PRIMARY KEY,
  ManagerID INT REFERENCES Employees(ID),
  Name VARCHAR(100)
);

In the above code, ManagerID is the foreign key linking back to the ID primary key, allowing us to indicate that one employee can be managed by another employee within the same organization. A virtual office at its finest!

Why use self-referencing keys?

Hierarchical data modeling

An intriguing aspect of self-referencing keys is its aptness in depicting hierarchical relationships. For instance, Employee table can effectively store manager-employee relationships. So, you can claim you're your own boss, though SQL may debunk that myth!

Noodle-angling a tree structure

The self-referencing concept is behind forming tree-like data structures using self-joins. Each row can be a "node" pointing to another "node". Imagine your family tree, but without the awkward family reunions.

Maintaining integrity and sanity

This practice encourages database normalization, cutting redundancy, and ensuring data integrity. Thus, your database becomes as orderly as your sock drawer (hopefully!).

Managing key relationships

Cascading: Friend or foe?

Working with self-references, think about "Cascading Updates" and "Deletes". Without these, orphaned rows could surface when a row gets deleted. Think of cascading as your own personal database nanny, keeping things tidy!

Self-referencing: Code à la carte

Consider a restaurant hierarchy where waitstaff reports to a supervisor, who reports to the manager. SQL Server's got your back, and here's the receipt:

-- Chef's special: Organization hierarchy à la SQL Server
CREATE TABLE RestaurantStaff (
  StaffID INT PRIMARY KEY,
  SupervisorID INT,
  Name VARCHAR(100),
  CONSTRAINT fk_supervisor
    FOREIGN KEY (SupervisorID)
    REFERENCES RestaurantStaff(StaffID)
    ON DELETE SET NULL -- Staff becomes "independent" after supervisor leaves. 
);

Predictable as a rerun sitcom, StaffID serves as the PK and SupervisorID is the FK.

Caution: Database at work!

In a real-life scenario, these key patterns need careful handling. Avoid setting the same column as both PK and FK unless it's necessary. The takeaway? Double-check your work, or your database might get as tangled as last year's Christmas lights!

Cracking the code on self-joins

Data retrieval is the name of the game, and self-joins are your best players. Here's the big play:

-- A peek into the corporate world. No corporate backstabbing, I promise!
SELECT e1.Name AS Staff, IFNULL(e2.Name, 'Top Brass') AS Supervisor
FROM RestaurantStaff e1
LEFT JOIN RestaurantStaff e2 ON e1.SupervisorID = e2.StaffID;

This join forms a relationship based on the similarity within the table and presents a clear view of the corporate ladder.

Untangling nested data

Did you know SQL can nest like a boss? There you have it, a nested data list that visualizes the hierarchy:

-- Behind the scenes of your favorite restaurant!
WITH RECURSIVE subordinates AS (
  SELECT StaffID, Name, SupervisorID
  FROM RestaurantStaff
  WHERE SupervisorID IS NULL -- The Big Boss starts here!
  UNION ALL
  SELECT e1.StaffID, e1.Name, e1.SupervisorID 
  FROM RestaurantStaff e1 
  INNER JOIN subordinates s1 ON e1.SupervisorID = s1.StaffID 
) 
SELECT * FROM subordinates;