主页 / dba / 问题 / 29170
Accepted
Grijesh Chauhan
Asked: 2012-11-24 04:01:08 +0800 CST

在MYSQL中插入自引用的限制？

  • 772

如何限制插入在递归关系表中添加自引用行（表本身包含外键点）？ 

mysql> SELECT * FROM Employee;
+-----+------+-------------+------+
| SSN | name | designation | MSSN |
+-----+------+-------------+------+
| 1   | A    | OWNER       | NULL |
| 2   | B    | BOSS        | 1    |
| 3   | C    | WORKER      | 2    |
| 4   | D    | BOSS        | 2    |
| 5   | E    | WORKER      | 4    |
| 6   | F    | WORKER      | 1    |
| 7   | G    | WORKER      | 4    |
| 8   | H    | BOSS        | 8    |
+-----+------+-------------+------+
8 rows in set (0.00 sec)

员工不能BOSS独善其身。因此 

mysql> INSERT INTO Employee VALUES ("8", "H", "BOSS",   "8");  
Query OK, 1 row affected (0.04 sec)

应该通过某种约束来拒绝。如何添加这样的约束？

如果可能，建议不使用触发器。

mysql mysql-5.5

1 个回答

  1. Best Answer

    这可以通过检查约束轻松解决，但这些尚未在 MySQL 中实现（它们只是在CREATE TABLE定义期间出于“兼容性原因”进行解析，之后完全被忽略。）

    所以，解决方案1是相当明显的。转移到已CHECK实现约束的 DBMS，例如 SQL-Server、Oracle、Postgres 等（甚至 MS-Access 也有它们！）：

    ALTER TABLE Employee
  ADD CONSTRAINT Employee_cannot_be_Boss_of_himself
    CHECK (mssn <> ssn) ;

    解决方案 2是完全删除该designation列。根据您的描述，所有'Owners'内容都为 null mssn，所有其他人的“经理”都ssn存储在mssn列中。

    删除该列后，您始终可以在视图中计算它：

    CREATE VIEW EmployeeDesignated AS
  SELECT ssn
       , name
       , CASE WHEN (mssn IS NULL OR mssn = ssn) 
                THEN 'OWNER'
              WHEN EXISTS (SELECT * FROM Employee ew WHERE ew.mssn = e.ssn)  
                THEN 'BOSS'
              ELSE 'WORKER'
         END AS designation
       , mssn
  FROM Employee AS e ;

    这只是强制在插入新员工时，如果 与mssn相同ssn，则将员工分配为“所有者”。

    不过，您可能会遇到性能问题，具体取决于您使用该designation列的方式。视图内部的视图或复杂查询对于 MySQL 的优化器来说并不是最好的选择。

    解决方案 3CHECK是使用此答案中描述的方法模拟约束：检查约束不起作用？

    这要求您升级到 MySQL 版本 5.7 或 MariaDB 5.5。

    解决方案 4是等待它们实施。有传言（现在是 2016 年）它们将出现在下一个版本中。

    • 2

相关问题