Create `CheckConstraint` in` UniqueConstraint`
Before creating a new record, I want to check the condition that the combination is native_linux_user
and is is_active
unique, but is_active
must be True
. Several native_linux_user
s is_active=False
can exist, but only one native_linux_user
s can exist is_active=True
.
I tried using CheckConstraint
internally 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'
),
)
source to share
From the SQL side, you can create a partial index UNIQUE
:
CREATE UNIQUE INDEX idx_unique_native_linux_user_is_active
ON table (native_linux_user) WHERE is_active=True;
And the SQLAlchemy model with the 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),
),
)
source to share