Can't create a recursive relationship in MySQL-CodePudding

I need some help, I am trying to create a recursive relation using the code below, but keep getting an error

CREATE TABLE `Employee` (
  `SSN` int,
  `address` varchar(50),
  `salary` varchar(50),
  `sex` varchar(50),
  `birthDate` varchar(50),
  `dependantId` int,
  `supervisorId` int,
  PRIMARY KEY (`SSN`),
  FOREIGN KEY (`dependantId`) REFERENCES `Dependant`(`dependantId`),
  FOREIGN KEY (`supervisorId`) REFERENCES `Employee`(`supervisorId`)
);

This is the error I'm getting: #1005 - Can't create table company recursive.employee (errno: 150 "Foreign key constraint is incorrectly formed")

CodePudding user response：

The table needs an ID column to be "referenceable" by foreign key constraints. That column musst be UNIQUE and ideally NOT NULL as well. A typical solution would look like:

CREATE TABLE Employee (
  `SSN` int,
  `address` varchar(50),
  `salary` varchar(50),
  `sex` varchar(50),
  `birthDate` varchar(50),
  id int not null unique, -- non nullable unique (acts as a key)
  dependantId int, -- nullable reference
  supervisorId int, -- nullable reference
  PRIMARY KEY (`SSN`),
  FOREIGN KEY (dependantId) REFERENCES Employee (id),
  FOREIGN KEY (supervisorId) REFERENCES Employee (id)
);

In this case dependantId and supervisorId can be null and become references that point to the same table Employee.

As a side note, typically hierarchies only include references to the supervisor and not to the dependant. In your case the latter is somewhat redundant and won't work well if a supervisor has more than one dependant.

CodePudding user response：

the reference should go to the SSN as it is the primary key

So you would get

CREATE TABLE `Employee` (
  `SSN` int,
  `address` varchar(50),
  `salary` varchar(50),
  `sex` varchar(50),
  `birthDate` varchar(50),
  `dependantId` int,
  `supervisorId` int,
  PRIMARY KEY (`SSN`),
  FOREIGN KEY (`dependantId`) REFERENCES `Employee`(`SSN`),
  FOREIGN KEY (`supervisorId`) REFERENCES `Employee`(`SSN`)
);

INSERT INTO Employee VALUES (1,'adreess1','10', 'male', '2000-01-01',NULL,NULL)

INSERT INTO Employee VALUES (2,'adreess1','10', 'male', '2000-01-01',NULL,1)

INSERT INTO Employee VALUES (3,'adreess1','10', 'male', '2000-01-01',2,1)

SELECT * FROM Employee

SSN | address  | salary | sex  | birthDate  | dependantId | supervisorId
--: | :------- | :----- | :--- | :--------- | ----------: | -----------:
  1 | adreess1 | 10     | male | 2000-01-01 |        null |         null
  2 | adreess1 | 10     | male | 2000-01-01 |        null |            1
  3 | adreess1 | 10     | male | 2000-01-01 |           2 |            1

db<>fiddle here