如何在MySQL/MariaDB约束中校验外键关联记录的字段值

MySQL（Debian Buster仓库提供的5.7版本）、MariaDB的CHECK约束仅支持基于当前表自身列的静态判断：

• 不允许嵌入跨表子查询，直接触发1064语法错误
• 不允许调用依赖其他表数据的自定义函数，非确定性表达式不符合CHECK约束要求，会触发1901错误
• Debian Buster自带的MySQL 5.7会直接忽略所有语法合法的CHECK约束，哪怕没有语法错误也不会生效

方案1：生成列+外键约束（推荐） #

属于SQL层面实现跨表引用校验的标准技巧，完全由约束保证逻辑，无触发器开销，不会遗漏边界场景。
实现逻辑：给tags表新增存储型生成列，仅当tag_meta为true时列值等于当前tag_id，否则为NULL；给该生成列加唯一约束后，tag_aliases的alias_id外键直接引用该生成列，只有元标签能被关联，tag_id仍可关联任意标签。

如果是新建表，可直接在tags的建表语句中集成生成列定义：

create table `tags` 
(
    `tag_id`           int           not null auto_increment,
    `tag_display_name` varchar(200)  not null unique,
    `tag_meta`         boolean       not null,
    `meta_tag_id`      int generated always as (if(`tag_meta`, `tag_id`, null)) stored,
    primary key (`tag_id`),
    unique key `uk_meta_tag_id` (`meta_tag_id`)
);

如果是修改现有表，执行以下SQL添加生成列和约束：

ALTER TABLE `tags`
ADD COLUMN `meta_tag_id` INT GENERATED ALWAYS AS (IF(`tag_meta`, `tag_id`, NULL)) STORED,
ADD UNIQUE KEY `uk_meta_tag_id` (`meta_tag_id`);

之后调整tag_aliases的外键：先执行SHOW CREATE TABLE tag_aliases;查看到旧的alias_id外键名，删除旧外键后新增指向生成列的外键：

-- 替换为实际查到的旧外键名
ALTER TABLE `tag_aliases` DROP FOREIGN KEY `tag_aliases_ibfk_1`;
ALTER TABLE `tag_aliases`
ADD CONSTRAINT `fk_tag_aliases_meta_tag` FOREIGN KEY (`alias_id`) REFERENCES `tags` (`meta_tag_id`);

该方案生效后的效果：

• 插入/更新tag_aliases时，若alias_id对应的标签tag_meta为false，外键无法匹配到非空的meta_tag_id值，直接操作失败
• tag_aliases的tag_id外键仍保留指向tags.tag_id，可关联任意标签，符合“元标签关联任意其他标签”的需求
• 若尝试将已被tag_aliases引用的标签的tag_meta从true改为false，对应的meta_tag_id会变为NULL，外键约束会阻止该修改，需先删除所有关联的tag_aliases记录才可调整

方案2：触发器实现 #

适合无法修改原有表结构、需要自定义错误提示的存量场景，通过触发器在数据写入时做校验。
具体SQL：

DELIMITER //

-- 插入tag_aliases前校验alias_id必须为元标签
CREATE TRIGGER `trg_tag_aliases_check_meta_insert`
BEFORE INSERT ON `tag_aliases`
FOR EACH ROW
BEGIN
    DECLARE is_meta BOOLEAN;
    SELECT `tag_meta` INTO is_meta FROM `tags` WHERE `tag_id` = NEW.`alias_id`;
    IF is_meta = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '仅元标签可作为alias_id关联其他标签';
    END IF;
END //

-- 更新tag_aliases时校验修改后的alias_id必须为元标签
CREATE TRIGGER `trg_tag_aliases_check_meta_update`
BEFORE UPDATE ON `tag_aliases`
FOR EACH ROW
BEGIN
    DECLARE is_meta BOOLEAN;
    IF NEW.`alias_id` != OLD.`alias_id` THEN
        SELECT `tag_meta` INTO is_meta FROM `tags` WHERE `tag_id` = NEW.`alias_id`;
        IF is_meta = 0 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '仅元标签可作为alias_id关联其他标签';
        END IF;
    END IF;
END //

-- 校验已被作为alias_id的标签不能修改为非元标签
CREATE TRIGGER `trg_tags_check_meta_update`
BEFORE UPDATE ON `tags`
FOR EACH ROW
BEGIN
    DECLARE has_alias INT;
    IF OLD.`tag_meta` = 1 AND NEW.`tag_meta` = 0 THEN
        SELECT COUNT(*) INTO has_alias FROM `tag_aliases` WHERE `alias_id` = OLD.`tag_id`;
        IF has_alias > 0 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该标签已被作为元标签关联，无法修改为非元标签';
        END IF;
    END IF;
END //

DELIMITER ;

该方案优缺点：灵活可自定义提示信息，无需调整原有表结构；但需维护多个触发器，容易遗漏边界场景，高并发下有额外性能开销。

• 生成列方案要求MySQL 5.7+、MariaDB 10.2+，Arch环境的MariaDB、Debian Buster自带的MySQL 5.7均满足版本要求
• 生产环境若使用MySQL 5.7，不要依赖任何CHECK约束的校验逻辑，所有业务规则需通过上述方案实现

内容的提问来源于stack exchange，提问作者Tomáš Růžička