SQL - Represent an unary (recursive) relationship

Question

I have to implement a unary relationship that works in this way:

There is an entity, called Employee and every Employee can be managed by another Employee until the boss which is not managed by anyone.

I tried this query:

CREATE TABLE employee
    (id         INTEGER PRIMARY KEY,
     name       VARCHAR(40) NOT NULL,
     managed_by INTEGER REFERENCES employee);

But does not work because, I think, in SQL a Foreign Key cannot reference its own entity.

How can I represent this recursive relationship in a correct way?

score 4 · Accepted Answer · answered Jan 02 '20 at 22:14

Yes, a foreign key can reference its own entity. This is a pretty common pattern.

Here's what it looks like:

CREATE TABLE `employee` (
  `id` int NOT NULL,
  `name` varchar(40) NOT NULL,
  `managed_by` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `managed_by` (`managed_by`),
  CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`managed_by`) REFERENCES `employee` (`id`)
);

Querying a recursive relationship is kind of tricky, though. You might like to read my answer to What is the most efficient/elegant way to parse a flat table into a tree?

SQL - Represent an unary (recursive) relationship

1 Answers1