如何在MySQL中实现反向外键检查（对外部表的约束）

为什么CHECK约束不生效？ #

• 第一种写法CHECK (columnNOT INOther Table)存在语法错误：NOT IN后必须跟括号包裹的取值列表或子查询，不能直接写表名。
• 第二种写法CHECK (columnNOT IN (SELECTother columnFROMOther Table))在MySQL中不被支持：MySQL的CHECK约束不允许包含子查询，即便8.0.16及以后版本支持CHECK，也无法用子查询实现跨表校验。

通用解决方案：使用触发器 #

触发器是实现这种跨表反向校验的通用方案，支持插入和更新操作时的实时检查。

针对你的Alias表场景（确保from列的值不存在于Mailbox表的name列），可以创建以下两个触发器：

1. 插入时校验的触发器 #

DELIMITER //
CREATE TRIGGER alias_before_insert_check
BEFORE INSERT ON `Alias`
FOR EACH ROW
BEGIN
    IF EXISTS (SELECT 1 FROM `Mailbox` WHERE `name` = NEW.`from`) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'from邮箱不能是已存在的邮箱账户';
    END IF;
END //
DELIMITER ;

2. 更新时校验的触发器 #

DELIMITER //
CREATE TRIGGER alias_before_update_check
BEFORE UPDATE ON `Alias`
FOR EACH ROW
BEGIN
    IF EXISTS (SELECT 1 FROM `Mailbox` WHERE `name` = NEW.`from`) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'from邮箱不能是已存在的邮箱账户';
    END IF;
END //
DELIMITER ;

说明：

• SIGNAL SQLSTATE '45000'用于抛出自定义错误，阻止插入/更新操作，45000是用户定义的错误码。
• 触发器会在每一行插入或更新前执行检查，确保数据符合要求。

可选方案：调整Schema实现约束（若允许） #

如果可以修改Schema结构，可以通过以下方式实现：

1. 创建一个包含所有允许作为from的邮箱值的表（比如AllowedAliasFrom），并给该表的邮箱列添加唯一约束。
2. 在Alias表的from列添加外键，关联AllowedAliasFrom的对应列。
3. 确保Mailbox表的name列和AllowedAliasFrom的邮箱列没有交集（可以通过触发器或应用层逻辑维护）。

不过这种方案需要额外维护一张表，通用性不如触发器方案。

内容的提问来源于stack exchange，提问作者Caulder