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?

Ilja Everilä · Accepted Answer · 2017-04-07T11:02:47.813

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'))

Great explanation! That makes a lot of sense. Do you see any arguments against sticking with the Index('my_table_uidx', attr_2, attr_3, unique=True) implementation? At the moment I tend towards staying with it, since it produces exactly the same MySQL definitions I had manually setup in MySQL Workbench previously, while also being straight forward to read on the python side. — Basti Vagabond, Apr 07 '17 at 11:06
I guess there's no reason to prefer one or the other, if using MySQL. Don't quote me on this, but unique constraints are implemented as unique indexes anyway. The same might not be true for all databases, though. Again I'm not familiar enough with the SQL standard to be sure. — Ilja Everilä, Apr 07 '17 at 11:16
Fair enough. That was my intuition as well. I'll post if I come across anything that suggests otherwise. Thanks! — Basti Vagabond, Apr 07 '17 at 11:23

slqlalchemy UniqueConstraint VS Index(unique=True)

1 Answers1

Linked