python - SQLalchemy 对多对多关系设置约束

Question

假设我有一组用户，每个用户都可以访问一组工具。同一工具可能有许多用户具有访问权限，因此这是多对多关系:

class User(db.Model):
    __tablename__ = 'user'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, unique=True)
    tools = db.relationship("Tool", secondary=user_tool_assoc_table,
                            back_populates='users')

class Tool(db.Model):
    __tablename__ = 'tool'
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, unique=False)

user_tool_assoc_table = db.Table('user_tool', db.Model.metadata,
    db.Column('user', db.Integer, db.ForeignKey('user.id')),
    db.Column('tool', db.Integer, db.ForeignKey('tool.id')))

注意用户名是唯一的，但工具名不是。所以 User.name:Mike1 和 User.name:Mike2 可能有权访问 Tool.name:Hammer，并且， User.name:John1 和 User.name:John2 可以使用相同的名称访问 Tool.name:Hammer 但每个都有不同的 Tool .ids.

我想限制在 User.tools 集合中永远不能有与另一个同名的工具，即

• 如果同名的工具已经存在，则用户不能创建新的工具作为其收藏的一部分。 Mike1 无法创建一个名为 Hammer 的新工具，该工具构成了他的 tools 集合的一部分。
• 数据库中存在的 Tool 不能附加到用户的 tools 集合中，如果集合中已经存在同名的工具，即 John1 的 >Hammer 无法与 Mike1 共享，因为 Mike1 已经拥有自己的 Hammer。
但是，
• James 可以创建一个新的 Hammer，因为他还没有锤子。然后数据库中将有 3 个名为 Hammer 的工具，每个工具都有一组不同的 Users。
• 请注意，在我的特定情况下，Tool 只有在至少有一个 User 时才会存在，但我也不知道如何在我的数据库中确保这一点.

这是否可以通过 SQLalchemy native 自动配置我的数据库以保持完整性？我不想编写自己的验证器规则，因为我可能会遗漏一些东西并最终得到一个违反我的规则的数据库。

Answer 1

问题是如何表达谓词“由 ID 标识的用户只有一个名为 NAME 的工具”。这当然很容易用一个简单的表格来表达，例如:

db.Table('user_toolname',
         db.Column('user', db.Integer, db.ForeignKey('user.id'), primary_key=True),
         db.Column('toolname', db.String, primary_key=True))

同样非常清楚的是，仅凭这一点还不足以维护完整性，因为用户工具名称与实际工具之间没有任何联系。您的数据库可能表明用户既有锤子也没有锤子。

最好在您的 user_tool_assoc_table 或类似的东西中强制执行此操作，但由于 Tool.name 不是 Tool< 的主键的一部分，你不能引用它。另一方面，由于您确实希望允许具有相同名称的多个工具共存，因此子集 { id, name } 实际上是 Tool 的正确键:

class Tool(db.Model):
    __tablename__ = 'tool'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String, primary_key=True)

id 现在充当具有相同名称的工具之间的各种“鉴别器”。请注意，id 在此模型中不必是全局唯一的，但对于 name 而言是局部的。让它自动递增仍然很方便，但默认设置为 autoincrement='auto'默认情况下仅将单列整数主键视为具有自动递增行为，因此必须显式设置。

现在也可以根据 tool_name 来定义 user_tool_assoc_table，附加的限制是用户只能拥有一个具有给定名称的工具:

user_tool_assoc_table = db.Table(
    'user_tool',
    db.Column('user', db.Integer, db.ForeignKey('user.id')),
    db.Column('tool', db.Integer),
    db.Column('name', db.String),
    db.ForeignKeyConstraint(['tool', 'name'],
                            ['tool.id', 'tool.name']),
    db.UniqueConstraint('user', 'name'))

使用此模型和以下设置:

john = User(name='John')
mark = User(name='Mark')
db.session.add_all([john, mark])
hammer1 = Tool(name='Hammer')
hammer2 = Tool(name='Hammer')
db.session.add_all([hammer1, hammer2])
db.session.commit()

这会成功:

john.tools.append(hammer1)
hammer2.users.append(mark)
db.session.commit()

在上述之后这将失败，因为它违反了唯一约束:

john.tools.append(hammer2)
db.session.commit()