CODEWITHSUNDEEP
pythonsqlalchemyflask-sqlalchemy
freethrow
freethrow

Reputation: 1108

How is a unique constraint across three columns defined?

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?

Upvotes: 3

Views: 5369

Answers (2)

Fabio S.
Fabio S.

Reputation: 41

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

Upvotes: 4

davidism
davidism

Reputation: 127410

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.

Upvotes: 4

Related Questions