求助编写MySQL CHECK约束：实现表中fieldA与fieldB的关联规则

方案适用性确认 #

MySQL 8.0.16及以上版本正式支持强制执行CHECK约束，完全满足你将应用层校验迁移到数据库层面的需求。低于该版本的MySQL会解析CHECK约束语法但不会实际生效，因此需确保你的MySQL版本符合要求。

具体实现逻辑 #

将你的规则转化为逻辑表达式，整合为表级CHECK约束（因涉及两个字段的关联校验，表级约束更合适）：

• 当fieldA为1或3时，fieldB必须为0
• 当fieldA为2时，fieldB需在1-3之间
• 当fieldA为4或5时，fieldB必须为3
• 同时限制fieldA取值范围为1-5，fieldB取值范围为0-3（覆盖规则中允许fieldB为0的场景）

对应的逻辑表达式：

CHECK (
    (fieldA BETWEEN 1 AND 5)
    AND (fieldB BETWEEN 0 AND 3)
    AND (
        (fieldA IN (1, 3) AND fieldB = 0)
        OR (fieldA = 2 AND fieldB BETWEEN 1 AND 3)
        OR (fieldA IN (4, 5) AND fieldB = 3)
    )
)

实际SQL示例 #

1. 创建新表时添加约束 #

CREATE TABLE your_table_name (
    id INT PRIMARY KEY AUTO_INCREMENT,
    fieldA INT,
    fieldB INT,
    CHECK (
        (fieldA BETWEEN 1 AND 5)
        AND (fieldB BETWEEN 0 AND 3)
        AND (
            (fieldA IN (1, 3) AND fieldB = 0)
            OR (fieldA = 2 AND fieldB BETWEEN 1 AND 3)
            OR (fieldA IN (4, 5) AND fieldB = 3)
        )
    )
);

2. 给现有表添加约束 #

ALTER TABLE your_table_name
ADD CONSTRAINT chk_fieldA_fieldB
CHECK (
    (fieldA BETWEEN 1 AND 5)
    AND (fieldB BETWEEN 0 AND 3)
    AND (
        (fieldA IN (1, 3) AND fieldB = 0)
        OR (fieldA = 2 AND fieldB BETWEEN 1 AND 3)
        OR (fieldA IN (4, 5) AND fieldB = 3)
    )
);

验证约束生效 #

插入符合规则的数据会成功：

INSERT INTO your_table_name (fieldA, fieldB) VALUES (1, 0); -- 成功
INSERT INTO your_table_name (fieldA, fieldB) VALUES (2, 2); -- 成功
INSERT INTO your_table_name (fieldA, fieldB) VALUES (5, 3); -- 成功

插入不符合规则的数据会触发错误：

INSERT INTO your_table_name (fieldA, fieldB) VALUES (1, 1); -- 失败：fieldA=1时fieldB必须为0
INSERT INTO your_table_name (fieldA, fieldB) VALUES (4, 2); -- 失败：fieldA=4时fieldB必须为3
INSERT INTO your_table_name (fieldA, fieldB) VALUES (6, 3); -- 失败：fieldA超出1-5范围

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