CODEWITHSUNDEEP
mysqlsqldatabasehierarchical-data
Simone C.
Simone C.

Reputation: 369

SQL - Represent an unary (recursive) relationship

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?

Upvotes: 2

Views: 5131

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562771

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?

Upvotes: 4

Related Questions