Create a `CheckConstraint` within a `UniqueConstraint`

Question

Before creating a new record I want to check a condition that the combination of native_linux_user and is_active is unique but is_active must be True. Multiple native_linux_user with is_active=False may exist, but only one native_linux_user with is_active=True can exist.

I tried to use a CheckConstraint within a UniqueConstraint like this, but it didn't work. How do I make this type of constraint?

 __table_args__ = (
     UniqueConstraint(
         'native_linux_user',
         CheckConstraint('is_active=True', name='active_check'),
         name='_username_uniqueness'
     ),
 )

Answer 1

From SQL side you can create partial UNIQUE index:

CREATE UNIQUE INDEX idx_unique_native_linux_user_is_active
    ON table (native_linux_user) WHERE is_active=True;

And SQLAlchemy model with corresponding Index:

class Foo(Model):
    id = Column(Integer, primary_key=True)
    native_linux_user = Column(String, nullable=False)
    is_active = Column(Boolean)

    __table_args__ = (
        Index('idx_unique_native_linux_user_is_active', native_linux_user,
            unique=True,
            postgresql_where=(is_active==True),
        ),
    )