4

In my flask project I need a table with a unique constraint on a column, if the values in an other column are identical. So I try to do something like that: 

if premiumuser_id = "a value I don't know in advance" then track_id=unique

This is similar to Creating partial unique index with sqlalchemy on Postgres, but I use sqlite (where partial indexes should also be possible: https://docs.sqlalchemy.org/en/13/dialects/sqlite.html?highlight=partial%20indexes#partial-indexes) and the condition is different.

So far my code looks like that:

class Queue(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    track_id = db.Column(db.Integer)

    premiumuser_id = db.Column(
        db.Integer, db.ForeignKey("premium_user.id"), nullable=False
    )

    __table_args__ = db.Index(
        "idx_partially_unique_track",
        "track_id",
        unique=True,
        sqlite_where="and here I'm lost",
    )

All examples I've found operate with boolean or fixed values. How should the syntax for sqlite_where look like for the condition: premiumuser_id = "a value I don't know in advance"?

Because789
  • 41
  • 1
  • AFAIU the index's where clause should refer to a variable not known in advance. I don't think this is possible, since this clause goes into the construction of the table/index and is thus hard-coded upon table/index creation. You would need to alter the table/index to modify the value on the fly (not practical). Maybe you need to re-design the table with a specific column whose value will determine the index inclusion (like a boolean: `is_premium_user`), and modify that column on the fly instead? – Jean Monet Nov 15 '20 at 22:12

0 Answers0