SQL 自引用外键约束和删除

在本文中，我们将介绍SQL中的自引用外键约束以及如何使用它来删除相关数据。

阅读更多：SQL 教程

什么是自引用外键约束？

自引用外键约束是指在一个表中的一个列引用该表中的另一个列。这种关系在数据库中被广泛应用，用于表示父子关系、树形结构、员工和经理关系等。

例如，我们有一个名为”employees”的表，其中包含员工的ID、姓名和经理ID。我们可以通过将”manager_id”列设置为引用”employee_id”列来创建自引用外键约束。

如何创建自引用外键约束？

在创建表时，我们可以使用”ALTER TABLE”语句来添加自引用外键约束。语法如下：

ALTER TABLE 表名
ADD CONSTRAINT 外键名
FOREIGN KEY (当前表的列名)
REFERENCES 表名(引用表的列名);

我们可以使用以下SQL语句来创建一个自引用外键约束的例子：

ALTER TABLE employees
ADD CONSTRAINT fk_manager
FOREIGN KEY (manager_id)
REFERENCES employees(employee_id);

如何使用自引用外键约束删除相关数据？

使用自引用外键约束时，我们需要注意删除操作可能导致关联数据的问题。在删除表中的行之前，我们需要先删除与该行相关的所有子行。

假设我们需要删除一个特定员工及其所有下属。我们可以使用递归查询来获取所有下属的ID，然后使用DELETE语句来删除这些下属。

以下是一个示例SQL语句：

WITH 所有下属(ID) AS (
  SELECT employee_id FROM employees WHERE manager_id = 1
  UNION ALL
  SELECT e.employee_id FROM employees e INNER JOIN 所有下属 a ON e.manager_id = a.ID
)
DELETE FROM employees WHERE employee_id IN (SELECT ID FROM 所有下属);