如何在Oracle SQL中添加Check约束限制同一产品比例总和≤100

普通的列级或表级CHECK约束只能校验单条记录的条件，没法跨多条记录做聚合计算，所以得用触发器或者数据库特有的功能来实现同一产品的proportion总和不超过100的约束。以下是主流数据库的具体实现方案：

PostgreSQL 实现 #

PostgreSQL支持断言（Assertion），可以直接定义跨记录的全局约束：

CREATE ASSERTION check_proportion_sum
CHECK (
  NOT EXISTS (
    SELECT 1
    FROM Behaviour
    GROUP BY Product_name
    HAVING SUM(proportion) > 100
  )
);

如果你的PostgreSQL版本不支持断言（比如旧版本），可以用触发器替代：

1. 先创建触发函数：

CREATE OR REPLACE FUNCTION check_proportion_sum()
RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM Behaviour
    WHERE Product_name = NEW.Product_name
    GROUP BY Product_name
    HAVING SUM(proportion) + COALESCE(NEW.proportion, 0) > 100
  ) THEN
    RAISE EXCEPTION '产品 % 的占比总和不能超过100', NEW.Product_name;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 绑定触发器到Behaviour表：

CREATE TRIGGER trigger_check_proportion_sum
BEFORE INSERT OR UPDATE ON Behaviour
FOR EACH ROW
EXECUTE FUNCTION check_proportion_sum();

MySQL 实现 #

MySQL不支持断言，只能通过触发器来实现：

插入时校验 #

DELIMITER //
CREATE TRIGGER check_proportion_insert
BEFORE INSERT ON Behaviour
FOR EACH ROW
BEGIN
  DECLARE total DECIMAL(10,2);
  SELECT COALESCE(SUM(proportion), 0) INTO total
  FROM Behaviour
  WHERE Product_name = NEW.Product_name;
  IF total + NEW.proportion > 100 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '当前产品的占比总和不能超过100';
  END IF;
END //
DELIMITER ;

更新时校验 #

DELIMITER //
CREATE TRIGGER check_proportion_update
BEFORE UPDATE ON Behaviour
FOR EACH ROW
BEGIN
  DECLARE total DECIMAL(10,2);
  -- 计算更新后的总和：原总和减去旧值加上新值
  SELECT COALESCE(SUM(proportion), 0) - OLD.proportion + NEW.proportion INTO total
  FROM Behaviour
  WHERE Product_name = NEW.Product_name;
  IF total > 100 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '当前产品的占比总和不能超过100';
  END IF;
END //
DELIMITER ;

SQL Server 实现 #

触发器方式 #

CREATE TRIGGER check_proportion_sum
ON Behaviour
AFTER INSERT, UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  IF EXISTS (
    SELECT Product_name
    FROM Behaviour
    GROUP BY Product_name
    HAVING SUM(proportion) > 100
  )
  BEGIN
    RAISERROR('产品的占比总和不能超过100', 16, 1);
    ROLLBACK TRANSACTION;
  END
END;

索引视图方式（性能更优） #

这种方式通过索引视图实时维护总和，约束校验更高效：

1. 创建绑定架构的视图：

CREATE VIEW vw_Behaviour_proportion
WITH SCHEMABINDING
AS
SELECT Product_name, SUM(proportion) AS total_proportion, COUNT_BIG(*) AS record_count
FROM dbo.Behaviour
GROUP BY Product_name;

2. 创建唯一聚集索引：

CREATE UNIQUE CLUSTERED INDEX idx_vw_behaviour_proportion ON vw_Behaviour_proportion(Product_name);

3. 添加CHECK约束：

ALTER VIEW vw_Behaviour_proportion
ADD CONSTRAINT chk_total_proportion CHECK (total_proportion <= 100);

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