如何在SQLAlchemy中确保多对多映射实体的唯一组合（PostgreSQL）

我有以下带关联表的SQLAlchemy模型，用于表示聊天会话，每个聊天可包含0到N个用户：

class Chat(BaseDbModel):
    __tablename__ = "chats"

    id = Column(Integer, primary_key=True)        
    users = relationship("User", secondary=chat_user_association, back_populates="chats")
    ...

class User(BaseDbModel):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    chats = relationship("Chat", secondary=chat_user_association, back_populates="users")
    ... 

chat_user_association = Table(
    'chat_user',
    BaseDbModel.metadata,
    Column('chat_id', Integer, ForeignKey('chats.id')),
    Column('user_id', Integer, ForeignKey('users.id')),
)

当前没有机制阻止创建包含相同用户ID集合的多个聊天，我希望彻底避免这种情况，理想是在数据库层面实现。

预期行为示例：

允许创建用户ID集合为{1,2}、{1,2,3}、{2,3}的聊天，但无法创建用户ID集合为{2,1,3}的聊天，因为这属于重复。

我可以在创建Chat对象时手动检查，但更希望采用能防止竞态条件的方案，我使用PostgreSQL。

核心思路 #

利用PostgreSQL的数组聚合与排序特性，给Chat表添加一个持久化的生成列，存储该聊天关联用户ID的排序数组，并对这个列添加唯一约束。这样无论用户添加顺序如何，相同的用户集合都会生成完全一致的排序数组，数据库层面的唯一约束会直接拦截重复创建，从根源避免竞态问题。

具体实现 #

修改Chat模型，新增生成列并添加唯一约束：

from sqlalchemy.dialects.postgresql import ARRAY
from sqlalchemy import Computed

class Chat(BaseDbModel):
    __tablename__ = "chats"

    id = Column(Integer, primary_key=True)        
    users = relationship("User", secondary=chat_user_association, back_populates="chats")
    # 新增持久化生成列：存储排序后的用户ID数组
    sorted_user_ids = Column(
        ARRAY(Integer),
        Computed(
            "(SELECT ARRAY_AGG(user_id ORDER BY user_id) FROM chat_user WHERE chat_id = chats.id)",
            persisted=True
        ),
        unique=True,
        nullable=True  # 允许空数组（对应无用户的聊天）
    )
    ...

方案优势 #

1. 数据库层面拦截：唯一约束直接在PostgreSQL生效，完全避免应用层检查的竞态问题（比如多请求并发创建相同用户集合的聊天）。
2. 自动维护：生成列的值由数据库自动计算和更新，无需手动维护，当聊天的用户关联发生变化时，sorted_user_ids会自动同步更新。
3. 简洁高效：无需修改关联表结构，仅需新增一个列即可实现需求，性能开销极低（持久化列仅在数据变更时计算）。

注意事项 #

• 确保使用的SQLAlchemy版本≥1.3（支持Computed列），PostgreSQL版本≥9.4（支持数组聚合函数ARRAY_AGG）。
• 如果需要允许多个无用户的聊天，可移除unique=True或者调整生成列的逻辑（比如给空集合分配唯一标识，但通常无用户的聊天场景较少）。

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