如何定义跨三列的唯一约束? [英] How is a unique constraint across three columns defined?

问题描述

以下 EventInvitation 模型是一个事件的简单邀请，从一个用户发送给另一个用户.我想确保邀请在三列中是唯一的:to_user、from_user 和 event.

The following EventInvitation model is a simple invitation for one event, sent from a user to another user. I would like to ensure that the invitations are unique across three columns: to_user, from_user and event.

class EventInvitation(db.Model):
    __tablename__ = 'event_invitations'

    id = db.Column(db.Integer, primary_key = True)

    event_id = db.Column(db.Integer, db.ForeignKey('events.id'))
    event = db.relationship('Event',  foreign_keys=[event_id])
    created = db.Column(db.DateTime(), default=datetime.now)
    updated = db.Column(db.DateTime(), default=datetime.now,onupdate=datetime.now)

    from_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    from_user = db.relationship('User',  foreign_keys=[from_id])

    to_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    to_user = db.relationship('User',  foreign_keys=[to_id])

    cstrt = db.UniqueConstraint('event_id', 'from_id','to_id', name='uix_1')

我尝试使用这个 cstrt 列，但它不起作用.该约束应该适用于 SQLite，以及生产中的 MySQL.如何定义此唯一约束?

I tried with this cstrt column but it doesn't work. The constraint should work on SQLite, as well as on MySQL in production. How can I define this unique constraint?

推荐答案

您需要将约束添加到表中，而不是模型中.要使用声明式执行此操作:

You need to add the constraint to the table, not the model. To do this using declarative:

class EventInvitation(db.Model):
    # ...
    __table_args__ = (
        db.UniqueConstraint(event_id, from_id, to_id),
    )

如果该表已在数据库中创建，则您需要删除该表并再次运行 db.create_all()，或使用 Alembic 通过迁移更改现有表.

If the table has already been created in the database, you'll need to drop the table and run db.create_all() again, or use Alembic to alter the existing table with a migration.

这篇关于如何定义跨三列的唯一约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！