如何添加检查多行数据的约束？MySQL多行列级约束实现求助

在MySQL中，CHECK约束仅能校验当前行数据，无法直接实现跨行统计的约束逻辑。要满足同一property_id对应的所有share字段总和不超过1的需求，可采用以下两种可行方案：

方案一：使用触发器（推荐） #

通过创建BEFORE INSERT和BEFORE UPDATE触发器，在数据变更前统计对应房产的所有权比例总和，判断是否符合约束要求。

1. 创建校验函数 #

先定义一个函数用于计算并校验总和，也可直接将逻辑写在触发器内：

DELIMITER //
CREATE FUNCTION check_share_sum(p_property_id INT, p_new_share DECIMAL(5,4))
RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    DECLARE total_share DECIMAL(5,4);
    -- 统计当前该房产的已分配比例总和，无数据则取0
    SELECT COALESCE(SUM(share), 0) INTO total_share
    FROM ownership
    WHERE property_id = p_property_id;
    
    -- 校验新增/更新后的总和是否不超过1
    RETURN (total_share + p_new_share) <= 1.0;
END //
DELIMITER ;

2. 创建插入触发器 #

拦截不符合约束的插入操作：

DELIMITER //
CREATE TRIGGER trg_ownership_insert_check
BEFORE INSERT ON ownership
FOR EACH ROW
BEGIN
    IF NOT check_share_sum(NEW.property_id, NEW.share) THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '同一房产的所有权比例总和不能超过1';
    END IF;
END //
DELIMITER ;

3. 创建更新触发器 #

处理更新场景时，要先减去旧的比例再校验：

DELIMITER //
CREATE TRIGGER trg_ownership_update_check
BEFORE UPDATE ON ownership
FOR EACH ROW
BEGIN
    DECLARE current_total DECIMAL(5,4);
    -- 计算移除旧比例后的当前总和
    SELECT COALESCE(SUM(share), 0) - OLD.share INTO current_total
    FROM ownership
    WHERE property_id = NEW.property_id;
    
    IF (current_total + NEW.share) > 1.0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '同一房产的所有权比例总和不能超过1';
    END IF;
END //
DELIMITER ;

方案二：汇总表+CHECK约束 #

创建一个存储各房产比例总和的汇总表，通过触发器维护汇总表数据，再利用CHECK约束限制总和上限。

1. 创建汇总表 #

CREATE TABLE property_share_total (
    property_id INT PRIMARY KEY,
    total_share DECIMAL(5,4) NOT NULL,
    -- 直接在汇总表加约束限制总和不超过1
    CONSTRAINT chk_total_share CHECK (total_share <= 1.0 AND total_share >= 0)
);

2. 创建触发器维护汇总表 #

覆盖所有数据变更场景，保证汇总表与原表数据一致：

DELIMITER //
-- 插入数据时更新汇总表
CREATE TRIGGER trg_property_share_insert
AFTER INSERT ON ownership
FOR EACH ROW
BEGIN
    INSERT INTO property_share_total (property_id, total_share)
    VALUES (NEW.property_id, NEW.share)
    ON DUPLICATE KEY UPDATE total_share = total_share + NEW.share;
END //

-- 更新数据时更新汇总表
CREATE TRIGGER trg_property_share_update
AFTER UPDATE ON ownership
FOR EACH ROW
BEGIN
    UPDATE property_share_total
    SET total_share = total_share - OLD.share + NEW.share
    WHERE property_id = NEW.property_id;
END //

-- 删除数据时更新汇总表
CREATE TRIGGER trg_property_share_delete
AFTER DELETE ON ownership
FOR EACH ROW
BEGIN
    UPDATE property_share_total
    SET total_share = total_share - OLD.share
    WHERE property_id = OLD.property_id;
    -- 可选：当总和为0时删除汇总表记录
    DELETE FROM property_share_total WHERE total_share = 0;
END //
DELIMITER ;

注意事项 #

• 并发场景下，触发器可能存在竞态问题，可通过提升事务隔离级别或加行锁来避免总和超限。
• 方案二中需确保触发器覆盖所有数据变更操作，否则会导致汇总表与原表数据不一致。

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