PostgreSQL：为卡片表添加约束，限制用户存在有效状态卡片时不可新建

你的需求是：当用户已存在状态为ACTIVE或LOCKED的卡片时，禁止为该用户创建任何新卡片。这种业务规则无法通过部分唯一索引或GIST排除约束直接实现，因为这两种方式只能限制特定状态下的唯一性，无法阻止插入其他状态的卡片。下面是具体的实现方案：

步骤1：创建触发器函数 #

这个函数会在插入新卡片前，检查目标用户是否已有ACTIVE或LOCKED状态的卡片，如果存在则抛出异常阻止插入。

CREATE OR REPLACE FUNCTION check_user_active_locked_card()
RETURNS TRIGGER AS $$
BEGIN
    -- 检查当前用户是否存在符合条件的卡片
    IF EXISTS (
        SELECT 1 FROM card
        WHERE userId = NEW.userId
        AND cardState IN ('ACTIVE', 'LOCKED')
    ) THEN
        RAISE EXCEPTION '用户 % 已存在ACTIVE或LOCKED状态的卡片，无法创建新卡片', NEW.userId;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤2：创建BEFORE INSERT触发器 #

将上面的函数绑定到card表的插入操作前，确保每次插入新记录时都执行检查：

CREATE TRIGGER trigger_check_user_card
BEFORE INSERT ON card
FOR EACH ROW
EXECUTE FUNCTION check_user_active_locked_card();

为什么之前的方法不适用？ #

• 部分唯一索引：比如CREATE UNIQUE INDEX idx_user_active ON card(userId) WHERE cardState IN ('ACTIVE', 'LOCKED');，只能保证同一个用户最多有一条ACTIVE或LOCKED的卡片，但允许插入其他状态（如INACTIVE）的新卡片，不符合你的需求。
• GIST排除约束：类似ALTER TABLE card ADD CONSTRAINT exclude_user_active EXCLUDE USING gist (userId WITH =) WHERE (cardState IN ('ACTIVE', 'LOCKED'));，效果和部分唯一索引一致，无法阻止插入非目标状态的新卡片。

测试验证 #

1. 插入用户1的ACTIVE卡片：

INSERT INTO card (cardId, userId, cardState) VALUES (1, 1, 'ACTIVE');

执行成功。

2. 尝试再次插入用户1的新卡片（无论状态）：

INSERT INTO card (cardId, userId, cardState) VALUES (2, 1, 'INACTIVE');

会抛出异常，阻止插入，符合预期。

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