外键可以引用它自己表的主键吗?

I am creating a MySQL employee database for work and I want it to store the supervisor of the employee.

我正在为工作创建一个MySQL雇员数据库，我希望它存储雇员的主管。

Suppose I have a table called 'employee' with the fields 'id', 'first_name', 'last_name', and 'supv_id' where 'id' is the primary key and 'supv_id' is a foreign key that refers to and employee ID.

假设我有一个名为“employee”的表，其中字段“id”、“first_name”、“last_name”和“supv_id”，其中“id”是主键，“supv_id”是外键，用于引用和员工id。

Currently I have 'supv_id' as a foreign key that points to a separate table 'supervisor'. This table simply consists of 'id' and 'empl_id' which points back to the employee table. However, if there is a way to simply make 'supv_id' in 'employee' to point to 'employee.id', this would eliminate the need of my 'supervisor' table altogether. Here is an example:

目前，我将'supv_id'作为外键，指向一个单独的表'supervisor'。这个表由'id'和'empl_id'组成，它们指向employee表。但是，如果有一种方法可以让“employee”中的“supv_id”指向“employee”。id，这将消除我的“主管”表的需要。这是一个例子:

+----+--------+-----------+---------+
| id | f_name | l_name    | supv_id |
+----+--------+-----------+---------+ 
|  1 | Han    | Solo      |    NULL | //Or 0?
|  2 | Luke   | Skywalker |       1 |
+----+--------+-----------+---------+

In short, I want 'supv_id' to point to another employee. Does this make sense? How would I go about doing this?

简而言之，我希望'supv_id'指向另一个员工。这是否有意义吗?我该怎么做呢?

Thanks!

谢谢!

Edit: fixed table

编辑:固定表

3 个解决方案

#1

You can create such a table as following:

您可以创建如下表:

CREATE TABLE laya2 (
    id INT NOT NULL PRIMARY KEY,
    f_name VARCHAR(20),
    l_name VARCHAR(20),
    supv_id INT,
    INDEX supv_id_idx (supv_id),
    FOREIGN KEY (supv_id)
        REFERENCES laya2(id) 
        ON DELETE SET NULL      -- example for an action
) ENGINE=INNODB;

My example sets the reference option to SET NULL, because I think it's the logical one here. If an employee who supervises others left, then those employees have no supervisor first. Another option would be to have NO ACTION because you could easily identify those employees without a valid supervisor and find a new supervisor for them. ON DELETE CASCADE would be wrong here, because those employees won't leave at the same time ...

我的示例将引用选项设置为NULL，因为我认为它是这里的逻辑选项。如果一个监督别人的员工离开了，那么这些员工首先没有主管。另一种选择是不采取行动，因为你可以很容易地找到那些没有有效上司的员工，并为他们找到新的上司。在删除层叠将是错误的，因为那些雇员不会同时离开…

You could insert employees with

你可以插入员工。

INSERT INTO laya2 VALUES
(1, 'Han', 'Solo', NULL),
(2, 'Luke', 'Skywalker', 1);

(two successful inserts), but not with

(两次成功插入)，但没有

INSERT INTO laya2 VALUES
(3, 'Anakin', 'Skywalker', 0);

This statement will fail because the foreign key constraint fails.

由于外键约束失败，此语句将失败。

Deleting Han Solo will change the supv_id for Luke Skywalker to NULL, because of the reference option ON DELETE SET NULL

删除汉·索罗将把卢克·天行者的supv_id更改为NULL，因为删除SET NULL的引用选项

DELETE FROM laya2 WHERE id = 1;    -- this will set the supv_id for Luke Skywalker to NULL

#2

Yes, join the table to itself. Here's one of many ways:

是的，把桌子和它自己连接起来。这里有很多方法:

SELECT a.l_name AS employee, b.l_name AS supervisor 
  FROM employee AS a, employee AS b 
  WHERE a.supv_id = b.id             -- link tables
    AND a.id = 2                     -- get employee

Returns:

employee  | supervisor
----------+-----------
Skywalker | Solo

#3

Yes, you can define a foreign key that refers to the primary key of its own table.

是的，您可以定义一个外键，它引用自己表的主键。

create table employee (id int(10),
                       f_name varchar(10),
                       l_name varchar(10),
                       supv_id int(10)) ENGINE=InnoDB;
alter table employee add primary key (id);
alter table employee add foreign key (supv_id) references employee (id);

Employees without supervisor must have NULL in the supv_id column.

没有主管的员工必须在supv_id列中具有NULL。

#1