如何为表添加约束限制同一(category,year)组合最多插入3条数据？

原CHECK约束无法实现需求，因为CHECK是行级约束，只能验证当前行的字段值，无法对整个表的聚合结果（比如分组计数）进行校验，所以count(category, year)<3这种写法在CHECK约束中不生效。

以下是几种可行的实现方案：

方案1：触发器（通用支持，适用于MySQL、SQL Server、Oracle等） #

通过插入/更新前触发逻辑，统计目标组合的现有行数，超过限制则阻止操作。

MySQL 示例： #

-- 创建表
CREATE OR REPLACE TABLE nobelpreis (
  category VARCHAR(255),
  year INT,
  pnr INT
);

-- 创建触发器
DELIMITER //
CREATE TRIGGER check_max_tripple BEFORE INSERT ON nobelpreis
FOR EACH ROW
BEGIN
    DECLARE cnt INT;
    SELECT COUNT(*) INTO cnt FROM nobelpreis 
    WHERE category = NEW.category AND year = NEW.year;
    IF cnt >= 3 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '同一(category, year)组合最多只能插入3条记录';
    END IF;
END //
DELIMITER ;

PostgreSQL 示例： #

-- 创建表
CREATE OR REPLACE TABLE nobelpreis (
  category VARCHAR(255),
  year INT,
  pnr INT
);

-- 创建触发器函数
CREATE OR REPLACE FUNCTION check_max_tripple()
RETURNS TRIGGER AS $$
DECLARE
    cnt INT;
BEGIN
    SELECT COUNT(*) INTO cnt FROM nobelpreis 
    WHERE category = NEW.category AND year = NEW.year;
    IF cnt >= 3 THEN
        RAISE EXCEPTION '同一(category, year)组合最多只能插入3条记录';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器
CREATE TRIGGER trigger_check_max_tripple BEFORE INSERT ON nobelpreis
FOR EACH ROW EXECUTE FUNCTION check_max_tripple();

插入测试数据时，第4条同组合的记录会触发错误，符合预期。

方案2：PostgreSQL 专属：结合序号列与唯一约束 #

添加一个维护分组内序号的列，通过唯一约束+CHECK限制序号范围：

CREATE OR REPLACE TABLE nobelpreis (
  category VARCHAR(255),
  year INT,
  pnr INT,
  seq_num INT,
  -- 限制序号不超过3
  CONSTRAINT max_tripple CHECK (seq_num <= 3),
  -- 确保同一组合内序号唯一
  CONSTRAINT unique_seq UNIQUE (category, year, seq_num)
);

插入时需要手动指定seq_num（或通过触发器自动生成），比如同一(category, year)下的记录依次赋值1、2、3，超过则触发唯一约束或CHECK约束报错。

注意事项 #

• 如果允许修改category或year字段，需要额外添加BEFORE UPDATE触发器，防止将其他组合的记录修改为已达上限的组合。
• 不同数据库的触发器语法略有差异，需根据实际使用的数据库调整代码。

内容的提问来源于stack exchange，提问作者HoffErik-JohnerInst