MySQL中实现公立学校月费为NULL的表间约束方案咨询

由于MySQL不支持CREATE ASSERTION语句，你可以通过以下几种方式实现需求中的约束：

方法1：触发器（兼容所有MySQL版本） #

触发器是最可靠的跨表约束实现方式，能在数据插入、更新时实时校验规则，阻止非法操作。

1.1 拦截公立学校 enrolment 记录的非空月费插入/更新 #

创建两个触发器，分别处理Enrolment表的插入和更新场景：

DELIMITER //
-- 插入前校验
CREATE TRIGGER check_public_fee_insert
BEFORE INSERT ON Enrolment
FOR EACH ROW
BEGIN
    DECLARE school_type VARCHAR(20);
    SELECT Type INTO school_type FROM `Primary School` WHERE Name = NEW.School;
    IF school_type = 'Public' AND NEW.`Monthly Fee` IS NOT NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '公立学校的月费必须为NULL';
    END IF;
END //

-- 更新前校验
CREATE TRIGGER check_public_fee_update
BEFORE UPDATE ON Enrolment
FOR EACH ROW
BEGIN
    DECLARE school_type VARCHAR(20);
    SELECT Type INTO school_type FROM `Primary School` WHERE Name = NEW.School;
    IF school_type = 'Public' AND NEW.`Monthly Fee` IS NOT NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '公立学校的月费必须为NULL';
    END IF;
END //
DELIMITER ;

1.2 拦截存在非空月费记录的学校改为公立的操作 #

如果将非公立学校改为公立时，该学校已有非空月费的 enrolment 记录，需要阻止这个修改：

DELIMITER //
CREATE TRIGGER check_school_type_change
BEFORE UPDATE ON `Primary School`
FOR EACH ROW
BEGIN
    IF OLD.Type != 'Public' AND NEW.Type = 'Public' THEN
        IF EXISTS (SELECT 1 FROM Enrolment WHERE School = NEW.Name AND `Monthly Fee` IS NOT NULL) THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '无法将学校改为公立：该学校存在非空月费的入学记录';
        END IF;
    END IF;
END //
DELIMITER ;

方法2：CHECK约束+生成列（仅MySQL 8.0.16及以上版本） #

MySQL 8.0.16开始真正支持CHECK约束，但该约束无法直接引用其他表字段。可以通过存储生成列关联学校类型，再添加约束：

1. 给Enrolment表添加存储生成列，同步对应学校的类型：

ALTER TABLE Enrolment
ADD COLUMN school_type VARCHAR(20) GENERATED ALWAYS AS (
    (SELECT Type FROM `Primary School` WHERE Name = School)
) STORED;

2. 添加CHECK约束，确保公立学校的月费为NULL：

ALTER TABLE Enrolment
ADD CONSTRAINT chk_public_school_fee
CHECK (school_type != 'Public' OR `Monthly Fee` IS NULL);

注意：存储生成列不会自动同步Primary School表的类型变更，除非修改Enrolment表的School字段。如果学校类型可能频繁修改，建议优先使用触发器方案。

方法3：应用层校验 #

在业务代码中，每次执行Enrolment的插入/更新操作前，先查询对应学校的类型：

• 若学校类型为Public，强制将Monthly Fee设为NULL，或直接提示用户不能输入月费。
• 若修改学校类型为Public，先检查该学校是否存在非空月费的 enrolment 记录，存在则阻止操作。

这种方式需要确保所有数据库操作都通过应用层进行，避免直接操作数据库绕过校验。

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