sqlachemy UniqueConstraint with conditional?

Question

Is it possible to create a contraint on a table and specify a value on one or more of the columns? Condsider this example:

mytable = Table('mytable', meta,

    # per-column anonymous unique constraint
    Column('col1', Integer,),
    Column('col2', Integer),
    Column('col3', ENUM('ready', 'pass', 'fail'),

    UniqueConstraint('col2', 'col2', 'col3', name='uix_1')
    )

But I dont only want uniqueness when col3 is equal to something like a state of 'ready' (I WANT multiple success or failures).

    UniqueConstraint('col2', 'col2', 'col3 == ready', name='uix_1')

Is this possible in the sqlalchemy api?

If your database supports partial indexes, then yes: https://stackoverflow.com/questions/27976683/creating-partial-unique-index-with-sqlalchemy-on-postgres — Ilja Everilä, Mar 14 '18 at 22:02

score 12 · Accepted Answer · edited Jan 17 '19 at 12:57

12

There is a full example on this link:

class ExampleTable(Base):

    __tablename__ = 'example_table'
    __table_args__ = (
        Index(
            'ix_unique_primary_content',  # Index name
            'object_type', 'object_id',  # Columns which are part of the index
        unique=True,
        postgresql_where=Column('is_primary')),  # The condition
    )

    id = Column(Integer, primary_key=True)
    object_type = Column(Unicode(50))
    object_id = Column(Integer)
    is_primary = Column(Boolean)

so you can use something like this:

Index(
    'col1', 'col2',  # Columns which are part of the index
    unique=True,
    postgresql_where=Column("col3='ready'")),  # The condition

edited Jan 17 '19 at 12:57

Zuabi

1,112
1
13
26

answered Oct 10 '18 at 05:39

Mohsenasm

2,916
1
18
22

can one via `Index` with `unique=True` enforce unique together like via using `UniqueConstraint `? – andilabs Oct 15 '19 at 14:55
I still able to add duplicated rows after adding this Index. What I'm doing wrong? – Mark Mishyn Nov 11 '21 at 14:52
@MarkMishyn I think it's a database-level check. Are you sure you had this index (with `unique=True`) when you created the table? I recommend checking that this index is correctly exists in the table schema (https://www.postgresqltutorial.com/postgresql-describe-table). – Mohsenasm Nov 12 '21 at 18:56
@Mohsenasm thanks, I will check. – Mark Mishyn Nov 13 '21 at 16:49
@Mohsenasm is this also possible for mysql? – Nauman Naeem Jul 01 '22 at 10:58

score 1 · Answer 2 · answered Mar 15 '18 at 15:20

So from what I understand you want the group (col1, col2, col3) to be unique only if col3 has the value 'ready'?

I don't think that is possible using unique constraints. It could be done with a CheckConstraint, assuming your database supports it.

You can read up on it here

sqlachemy UniqueConstraint with conditional?

2 Answers2