How is a unique constraint across three columns defined?

Question

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')

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?

Answer 1

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),
    )

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.

Answer 2

I don't agree with the accepted answer. That was not the reason why the code didn't work.

The reason why it was not working is because you quoted the name of the columns.

If you write:

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

Everything will work fine.

And of course it works also using table_args:

    __table_args__ = (
    db.UniqueConstraint(event_id, from_id, to_id),
)