MySQL 自我外键

MySQL自我外键是MySQL关系数据库中一种特殊的外键约束，它可以将表中的一个或多个列自身设置为外键。当一张表中的数据需要引用这张表中同一列的另一行时，可以使用MySQL自我外键来实现。

阅读更多：MySQL 教程

MySQL自我外键的适用场景

父子级表结构

在一些父子级表结构中，子表的每一行必须关联到该表的父级表中自己本身所属行。比如，department表，包含一列父级部门ID，和一列当前部门ID，在该表的数据需要形成一棵树的时候，使用自我外键可以直接建立父子级关系约束。

-- department表结构
CREATE TABLE department (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  parent_id INT
);

-- 在department表上创建自我外键
ALTER TABLE department ADD CONSTRAINT fk_department_self FOREIGN KEY (parent_id) REFERENCES department (id);

朋友关系表结构

在一些朋友关系表结构中，用户可以将自己与其他用户建立好友关系，好友关系可以是单向的，也可以是双向的。使用自我外键可以直接在一张表中建立这种朋友关系的限制。

-- user表结构
CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(255)
);

-- friend表结构
CREATE TABLE friend (
  user_id INT,
  friend_user_id INT,
  PRIMARY KEY (user_id, friend_user_id),
  FOREIGN KEY (user_id) REFERENCES user(id),
  FOREIGN KEY (friend_user_id) REFERENCES user(id)
);

-- 在friend表上创建自我外键
ALTER TABLE friend ADD CONSTRAINT fk_friend_self FOREIGN KEY (user_id) REFERENCES friend (friend_user_id);

MySQL自我外键的语法

在MySQL中，创建自我外键的语法与创建外键的方式是类似的，只需要将外键的参考列设为自己的列即可。

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
FOREIGN KEY (参考列) REFERENCES 表名（自己的列）;

MySQL自我外键的限制

只适用于InnoDB存储引擎

自我外键约束只适用于InnoDB存储引擎，因为它是InnoDB存储引擎的特性之一。如果使用MyISAM存储引擎，将无法使用自我外键约束。

自我外键与主键

自我外键不容许涉及主键列，否则此自我外键将无效。原因是主键列一定有一个唯一索引，而此唯一索引会与外键约束产生矛盾。

外键删除操作

删除表中某一行时，如果该行被其他行所引用，MySQL将不会自动地删除该行，而是会引发一个错误。因此，删除表中的行时，需要事先删除与之相关的自我外键约束。

MySQL自我外键的修改和删除

修改自我外键

在MySQL中，如果需要修改自我外键，可以先使用DROP CONSTRAINT语句删除原有的自我外键，再使用ADD CONSTRAINT语句添加修改后的自我外键。

ALTER TABLE table_name DROP CONSTRAINT foreign_key_name;
ALTER TABLE table_name ADD CONSTRAINT new_foreign_key_name FOREIGN KEY (column_name) REFERENCES table_name (self_column_name);

删除自我外键

在MySQL中，可以使用DROP CONSTRAINT语句删除自我外键约束。

ALTER TABLE table_name DROP CONSTRAINT foreign_key_name;

案例分析

本案例将介绍如何使用自我外键来创建一张简单的部门表，以实现部门管理。该表结构如下：

CREATE TABLE department(
  id INT PRIMARY KEY,
  name VARCHAR(255),
  parent_id INT,
  CONSTRAINT fk_department_self FOREIGN KEY (parent_id) REFERENCES department (id)
);

在该表结构中，id列为主键，parent_id列为自我外键，用于建立部门之间的父子级关系。

我们可以在该表中插入以下数据：

INSERT INTO department (id, name, parent_id) VALUES
(1, '总公司', NULL),
(2, '销售部', 1),
(3, '财务部', 1),
(4, '研发部', 1),
(5, '市场部', 4),
(6, '前端开发组', 5),
(7, '后端开发组', 5);

以上数据将会形成一颗以”总公司”为根节点的树形结构，如下所示：

总公司
├── 销售部
├── 财务部
└── 研发部
    ├── 市场部
    │   ├── 前端开发组
    │   └── 后端开发组

我们可以使用如下SQL查询语句来获取整张部门表的树形结构：

WITH RECURSIVE cte(id, name, parent_id, lvl) AS (
  SELECT id, name, parent_id, 0 FROM department WHERE parent_id IS NULL
  UNION ALL
  SELECT department.id, department.name, department.parent_id, lvl + 1 FROM department
  JOIN cte ON department.parent_id = cte.id
)
SELECT id, CONCAT(REPEAT('-', lvl * 2), name) AS name FROM cte
ORDER BY id;

该查询语句将会返回以下结果：

id | name
---|------------------
1  | 总公司
2  | -- 销售部
3  | -- 财务部
4  | -- 研发部
5  | ---- 市场部
6  | ------ 前端开发组
7  | ------ 后端开发组

总结

在MySQL中，使用自我外键可以方便地建立表中行之间的父子级关系，并可以轻松形成树形结构。此外，自我外键还能够限制表中的数据引用过程中的错误，从而更好地保证表的数据完整性。虽然使用自我外键有一定的限制，但对于父子级表结构和朋友关系表结构等场景来说，很多情况下MySQL自我外键都是非常有用的一种约束方式。