如何在SQL中限制表内域列中各域值的最大出现次数？

不需要依赖过程化语言也能实现，但触发器方式会更可靠，以下是两种可行方案：

方案一：函数+表级CHECK约束 #

先创建一个统计指定值出现次数的函数：

CREATE OR REPLACE FUNCTION count_pos_value(val pos)
RETURNS INTEGER AS $$
SELECT COUNT(*) FROM test_table WHERE position = val;
$$ LANGUAGE sql STABLE;

如果是新建表，直接在创建语句中添加CHECK约束：

CREATE TABLE test_table (
    id SERIAL PRIMARY KEY,
    position pos,
    CHECK (count_pos_value('cm') <= 2 AND count_pos_value('cf') <= 2)
);

如果是修改已有表，执行：

ALTER TABLE test_table
ADD CONSTRAINT pos_max_count CHECK (count_pos_value('cm') <= 2 AND count_pos_value('cf') <= 2);

注意：这种方式在并发插入/更新时可能出现竞态问题——比如两个事务同时插入第3个'cm'，各自统计时数量都是2，最终会导致超出限制。

方案二：触发器（避免竞态，更可靠） #

先写触发器函数，用来检查插入/更新操作是否违反数量限制：

CREATE OR REPLACE FUNCTION check_pos_max_count()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.position = 'cm' THEN
        IF (SELECT COUNT(*) FROM test_table WHERE position = 'cm') >= 2 THEN
            RAISE EXCEPTION 'cm的数量不能超过2个';
        END IF;
    ELSIF NEW.position = 'cf' THEN
        IF (SELECT COUNT(*) FROM test_table WHERE position = 'cf') >= 2 THEN
            RAISE EXCEPTION 'cf的数量不能超过2个';
        END IF;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

然后创建触发器，分别在插入和更新时触发检查：

-- 插入前检查
CREATE TRIGGER trigger_check_pos_insert
BEFORE INSERT ON test_table
FOR EACH ROW EXECUTE FUNCTION check_pos_max_count();

-- 更新position列前检查
CREATE TRIGGER trigger_check_pos_update
BEFORE UPDATE OF position ON test_table
FOR EACH ROW EXECUTE FUNCTION check_pos_max_count();

这种方案会在每一行插入或更新前实时检查对应值的当前数量，能有效避免并发场景下的违规操作。

你的pos域已经限制了列值只能是'cm'或'cf'，所以触发器里无需处理其他值的情况。

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