mysql - 关系表设计: prevent circular references

Question

我有一个问题，我不确定如何正确解决。我当前的设计包含两个表。员工和员工管理。我的 RDBMS 是 MySQL。

员工:

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

员工管理:

CREATE TABLE `employee_management` (
  `manager_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
   UNIQUE INDEX `association` (`manager_id`,`employee_id`),
   FOREIGN KEY (`manager_id`)
   REFERENCES employee(id)
   ON UPDATE CASCADE ON DELETE RESTRICT,
   FOREIGN KEY  (`employee_id`)
   REFERENCES employee(id)
   ON UPDATE CASCADE ON DELETE RESTRICT
) 

测试数据:

INSERT INTO employee(name) VALUES(
  'Bob Smith'
)
INSERT INTO employee(name) VALUES(
  'Bill Smith'
)

INSERT INTO employee_management(manager_id, employee_id) VALUES(
  1,2
)
INSERT INTO employee_management(manager_id, employee_id) VALUES(
  2,1
)

从 employee_management 选择行显示:

+------------+-------------+
| manager_id | employee_id |
+------------+-------------+
|          2 |           1 |
|          1 |           2 |
+------------+-------------+

返回的行表明 Bill Smith 管理 Bob Smith，而 Bob Smith 管理 Bill Smith，我认为这是一个循环引用。两个人互相管理是没有意义的。我认为 UNIQUE INDEX 会阻止插入具有任何现有值组合的行，但这不起作用。我知道我可以防止这种情况在应用程序级别发生，但我不确定这是否是合适的做法，但这是必须在应用程序级别强制执行的事情，还是我可以做些什么来防止循环引用？经理不应该也有经理。

Answer 1

造成这种复杂性的主要原因是对声明这些约束的支持不佳DBMS。

您只能装饰性地声明两种类型的表约束:

• 唯一标识属性(键)
• 引用回同一个表的子集需求，在这种情况下子集要求是一个表约束(相同的外键表)。

实现所有其他类型的表约束需要您开发程序数据完整性代码。实际上，这意味着您将经常不得不求助于应用程序业务层中的触发过程策略或实现。

正在关注this和 this post ，你可以使用触发器来检查下面的查询结果是否大于 0，然后回滚更改

SELECT count(*) FROM employee_management e1
            WHERE EXISTS (SELECT * FROM employee_management  e2
            WHERE
             e1.manager_id = e2.employee_id
             AND
            e1.employee_id = e2.manager_id )

作为脚注，您可能有一个从员工到员工的外键，显示员工的经理。