slqlalchemy UniqueConstraint VS Index(unique = True) [英] slqlalchemy UniqueConstraint VS Index(unique=True)

问题描述

我正在使用MySQL(运行InnoDB)，并使用sqlalchemy封装了整个程序.现在，我想通过使用(在 docs )

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

当我调用create_database时，我将获得sqlalchemy来创建具有指定的所有列的表"my_table".外键也可以很好地设置，但是在数据库端找不到唯一索引.然后，我尝试使用Index(unique = True)代替.因此，而不是

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

我放

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

我的印象是，这在逻辑上会产生相似的结果.这次sqlalchemy确实在数据库上创建了唯一索引.

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

也许我对UniqueConstraint和Index(unique = True)之间的区别或sqlalchemy使用它们来自动生成数据库的方式感到误解.

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?

推荐答案

主要区别在于， UniqueConstraint 和一般约束必须在表定义中内联定义:

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:

要将诸如 ForeignKeyConstraint 之类的表级约束对象应用于使用Declarative定义的表，请使用 __ table_args __ 属性，该属性在

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

要理解的是，在声明式类的构造过程中，如果未传递显式的 __ table __ ，则会构造一个新的 Table .在示例模型类中， UniqueConstraint 实例绑定到类属性，但是声明性基类不包含通过属性在创建的 Table 实例中的约束.您必须在表参数中传递它:

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

请注意，您必须将约束名称作为关键字参数传递.您还可以使用 Table.append_constraint() (如果在尝试创建表之前被调用):

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

这篇关于slqlalchemy UniqueConstraint VS Index(unique = True)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！