slqlalchemy UniqueConstraint VS Index(unique=True)

Question

I am using MySQL (running InnoDB), and wrapped the entire thing using sqlalchemy. Now, I would like to generate changes in my database by using (see docs)

sqlalchemy_utils.functions.create_database(...)

Generally the above function does what it is supposed to. The only exception being the generation of unique indexes.

Say, I define a table like this:

## ...
# DeclBase = declarative_base()
## ...
class MyTable(DeclBase):
    __tablename__ = 'my_table'

    id = Column(Integer, primary_key=True)
    attr_1 = Column(String(32))
    attr_2 = Column(Integer, nullable=False)
    attr_3 = Column(DateTime)
    attr_4 = Column(
        Integer,
        ForeignKey('other_table.id', onupdate='CASCADE', ondelete='CASCADE'),
        nullable=False
    )

    u_idx = UniqueConstraint(attr_2, attr_3, 'my_table_uidx')

when I call create_database I will get sqlalchemy to create the table 'my_table' with all columns as specified. The foreign key is also setup fine, but no unique index can be found on the database side. I then tried using a Index(unique=True) instead. So instead of

u_idx = UniqueConstraint(attr_2, attr_3, 'my_table_uidx')

I put

u_idx_1 = Index('my_table_uidx', attr_2, attr_3, unique=True)

My impression was this logically produces a similar result. This time sqlalchemy indeed created the unique index on the db.

Maybe I am miserably misunderstanding something about the difference between UniqueConstraint and Index(unique=True), or the way sqlalchemy uses them to automate generation of databases.

Can anyone shed some light on this?

Answer 1

The main difference is that while the Index API allows defining an index outside of a table definition as long as it can reference the table through the passed SQL constructs, a UniqueConstraint and constraints in general must be defined inline in the table definition:

To apply table-level constraint objects such as ForeignKeyConstraint to a table defined using Declarative, use the __table_args__ attribute, described at Table Configuration.

The thing to understand is that during construction of a declarative class a new Table is constructed, if not passed an explicit __table__. In your example model class the UniqueConstraint instance is bound to a class attribute, but the declarative base does not include constraints in the created Table instance from attributes. You must pass it in the table arguments:

class MyTable(DeclBase):
    __tablename__ = 'my_table'
    ...
    # A positional argument tuple, passed to Table constructor
    __table_args__ = (
        UniqueConstraint(attr_2, attr_3, name='my_table_uidx'),
    )

Note that you must pass the constraint name as a keyword argument. You could also pass the constraint using Table.append_constraint(), if called before any attempts to create the table:

class MyTable(DeclBase):
    ...

MyTable.__table__.append_constraint(
    UniqueConstraint('attr_2', 'attr_3', name='my_table_uidx'))