如何为SQL表添加约束：按三列分组且状态为IN_PROGRESS时仅一行primary为true

我来帮你搞定这个约束需求！你想要的效果是：当记录的status为IN_PROGRESS时，同一组（user_id, seat_id, account_id）内，primary列最多只能有一条记录设为true。

先看你给出的符合要求的示例表：

为什么你原来的CHECK约束行不通？ #

你写的CHECK约束是行级约束，它只能验证当前行的条件，没办法跨行检查同一分组内的其他记录。所以要实现这种跨行的分组唯一性，得用部分唯一索引（适合PostgreSQL）或者触发器（适合MySQL等不支持部分索引的数据库）。

方案1：PostgreSQL推荐实现（部分唯一索引） #

这是最简洁高效的方式，直接创建一个仅包含status = 'IN_PROGRESS'且primary = true记录的唯一索引，强制这三列的组合唯一：

CREATE UNIQUE INDEX users_unique_primary_in_progress
ON users (user_id, seat_id, account_id)
WHERE status = 'IN_PROGRESS' AND "primary" = true;

原理：这个索引只会作用于符合条件的记录，确保同一（user_id, seat_id, account_id）组合下，最多只能有一条primary = true且状态为IN_PROGRESS的记录。如果尝试插入或更新违反这个规则的数据，数据库会直接抛出错误。

方案2：MySQL下的实现方式（触发器） #

MySQL直到8.0.16才支持类似部分索引的功能，之前的版本需要用触发器来验证：

插入前验证触发器

DELIMITER //
CREATE TRIGGER check_primary_unique_insert
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.status = 'IN_PROGRESS' AND NEW.`primary` = true THEN
        -- 检查同组是否已有primary=true的IN_PROGRESS记录
        IF EXISTS (
            SELECT 1 FROM users
            WHERE user_id = NEW.user_id
              AND seat_id = NEW.seat_id
              AND account_id = NEW.account_id
              AND status = 'IN_PROGRESS'
              AND `primary` = true
        ) THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '错误：同一（user_id, seat_id, account_id）分组的IN_PROGRESS记录中，primary只能设为true一次';
        END IF;
    END IF;
END //
DELIMITER ;

更新前验证触发器

DELIMITER //
CREATE TRIGGER check_primary_unique_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    IF NEW.status = 'IN_PROGRESS' AND NEW.`primary` = true THEN
        -- 排除当前更新的行，避免自己和自己冲突
        IF EXISTS (
            SELECT 1 FROM users
            WHERE user_id = NEW.user_id
              AND seat_id = NEW.seat_id
              AND account_id = NEW.account_id
              AND status = 'IN_PROGRESS'
              AND `primary` = true
              AND id != NEW.id -- 假设表有主键id，没有的话用其他唯一标识
        ) THEN
            SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '错误：同一（user_id, seat_id, account_id）分组的IN_PROGRESS记录中，primary只能设为true一次';
        END IF;
    END IF;
END //
DELIMITER ;

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