确保SqlAlchemy中多对多映射实体的唯一组合

Question

我有一些SqlAlchemy模型，它们通过一个关联表来表示聊天对话。每个聊天可以有0到多个用户参与：

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}的另一个聊天，因为这会导致重复。

有什么最简单的解决方案或方法吗？我可以在创建聊天对象时手动检查，但我更希望有一种能更好地避免竞争条件的方法。我使用的是PostgreSQL。

Answer 1

一个简单的解决办法是对用户ID进行排序，这样可以确保它们的顺序是固定的，然后用这些ID来创建一个唯一的标识符。把这个标识符存储在一个必需的唯一列中，和聊天ID一起存放。可以这样做：

# Creating strings like "1-2", "1-2-3":
user_ids_key = '-'.join([str(user_id) for user_id in sorted(chatting_user_ids)])

然后在你的表中，你可以添加一个像这样的列：

class Chat(BaseDbModel):
    __tablename__ = "chats"

    id = Column(Integer, primary_key=True) 
    user_ids_key = Column(String, unique=True, nullable=False)
    #...

检查/异常情况

当插入重复的聊天时，这会抛出一个异常。虽然这样在创建新聊天之前可以更容易地检查是否存在，但你只需要检查这个异常，确保数据的完整性应该是有保障的。我很确定这个异常会在单个和多个唯一约束的情况下都被抛出：

sqlalchemy.exc.IntegrityError

维护完整性

为了保持唯一键的完整性，你必须小心不要以某种方式更改关联表，导致唯一键不同步。因此，要么聊天的“配置”，也就是用户ID的集合，是不可变的；要么在更新关联表时，你需要锁定聊天表的行。

• 使用PostgreSQL的更新锁，锁定聊天行，更新关联表，然后提交以释放锁
• 你可以进行软检查以防止冲突，比如你添加了一个用户或移除了一个用户，而你已经有了这个用户集合的聊天
• 在这种情况下应该发生什么似乎依赖于应用程序
  • 你是合并聊天，但消息中有一个用户ID被设置为NULL，以对应于一个已删除的用户吗？
  • 你使用软删除，账户被标记为已删除吗？
• 最彻底的解决方案可能是使用用户定义的数据库函数，但我总觉得这很难维护。我认为这可以作为后期的优化，可能会有更好的性能，并且可以去掉一些应用代码。不过我对这方面的经验不多。

局限性

我想这样做的一个缺点是，如果每个聊天中有很多用户，或者你的ID是非常大的数字，那么这可能会出现问题，但在那之前，这应该是一个可靠的解决方案。

其他约束

在保持数据库规范化的同样思路下，虽然我觉得这和你的问题不完全相关，但你可能想在关联表中对聊天ID和用户ID设置一个UniqueConstraint('chat_id', 'user_id')，并且为这两列设置nullable=False。

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