python - slqlalchemy UniqueConstraint VS Index(unique=True) -

i using mysql (running innodb), , wrapped entire thing using sqlalchemy. now, generate changes in database using (see docs)

sqlalchemy_utils.functions.create_database(...) 

generally above function supposed to. exception being generation of unique indexes.

say, define table 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 call create_database sqlalchemy create table 'my_table' columns specified. foreign key setup fine, no unique index can found on database side. tried using index(unique=true) instead. 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 logically produces similar result. time sqlalchemy indeed created unique index on db.

maybe miserably misunderstanding difference between uniqueconstraint , index(unique=true), or way sqlalchemy uses them automate generation of databases.

can shed light on this?

the main difference while index api allows defining index outside of table definition long can reference table through passed sql constructs, uniqueconstraint , constraints in general must defined inline in table definition:

to apply table-level constraint objects such foreignkeyconstraint table defined using declarative, use __table_args__ attribute, described @ table configuration.

the thing understand during construction of declarative class new table constructed, if not passed explicit __table__. in example model class uniqueconstraint instance bound class attribute, declarative base not include constraints in created table instance attributes. must pass in table arguments:

class mytable(declbase):     __tablename__ = 'my_table'     ...     # positional argument tuple, passed table constructor     __table_args__ = (         uniqueconstraint(attr_2, attr_3, name='my_table_uidx'),     ) 

note must pass constraint name keyword argument. pass constraint using table.append_constraint(), if called before attempts create table:

class mytable(declbase):     ...  mytable.__table__.append_constraint(     uniqueconstraint('attr_2', 'attr_3', name='my_table_uidx'))