Is it possible to set a Boolean column as a unique constraint in Flask-SQLalchemy?

Question

Is it possible to setup a table in such a way that only one record can be set to true at any given time?

I have a 'User' table and a 'Credentials' table. A User can have one or more login credentials associated to it. There should only be one "Active" credential at a time which is indicated by the "active" column(Boolean) in the Credentials table.

Is it possible to setup some kind of constraint to prevent two records from being "active" at the same time?

If you are using Postgresql, then yes: [Creating partial unique index with sqlalchemy on Postgres](https://stackoverflow.com/questions/27976683/creating-partial-unique-index-with-sqlalchemy-on-postgres/27979223) — Ilja Everilä, Oct 25 '18 at 06:09

EAW · Answer 1 · 2018-10-27T16:43:29.720

You can use a unique constraint to prevent 2 columns being active at the same time but you will have to use True and None as the possible values rather than True and False. This is because the unique constraint will only allow a single True in the column but would also only allow a single False as well. None (or SQL NULL) values do not participate in the unique constraint and therefore you can have as many of theses as you you have remaining rows. To ensure database integrity this is probably best achieved with a enum datatype with only a single possible value.

import enum

class Active(enum.Enum):
    true = True

class Credentials(db.Model):
    active = db.Column(db.Enum(Active), unique=True)

You can now to use Active.true as the value to indicate the active credential and None for all other credentials with integrity enforced at the database level. If you intended to have one active credential per user rather than one active credential in total this could be achieved with a separate UniqueConstraint statement.

class Credential(db.Model):
    __tablename__ = "credentials"
    __table_args__ = (db.UniqueConstraint('user_id', 'active'),)

    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('users.id'))
    user = db.relationship("User", back_populates="credentials")
    active = db.Column(db.Enum(Active))

class User(db.Model):
    __tablename__ = "users"

    id = db.Column(db.Integer, primary_key=True)
    credentials = db.relationship("Credential", back_populates="user")

While this does prevent more than one credential being marked as active it will not prevent a user from having no active credentials and you will need to rely on your application logic for this.

score 1 · Answer 2 · answered Oct 25 '18 at 07:41

So if i'm reading correctly you have two model classes User and Credential which is a one-to-many relationship:

class User(db.Model):
    ...
    credentials = db.relationship('Credential')

class Credential(db.Model):
    ...
    user_id = db.Column(db.Integer, db.ForeignKey('user.id', nullable=False)

Is it possible to add an additional foreignkey to signify a one-to-one relationship:

class User(db.Model):
    ...
    active_credential_id = db.Column(db.Integer, db.ForeignKey('credential.id'))

class Credential(db.Model):
    ...
    active_user = db.relationship('User')

You could update this with something like:

inactive_credential = # some credential from the user's list of credentials
user = User.query.filter(User.id == inactive_credential.user_id)
user.active_credential_id = inactive_credential.id
db.session.add(user)
db.session.commit()
# inactive_credential ==>> active_credential

The use of a foreign key here maintains database integrity.

You will need some additional constraint that says an active_credential_id can only be selected from the list of credentials whose user is defined by user_id. I just thought of this at the end and will update answer later if I have solution.

Is it possible to set a Boolean column as a unique constraint in Flask-SQLalchemy?

2 Answers2