sqlalchemy 在多列中是唯一的 [英] sqlalchemy unique across multiple columns
问题描述
假设我有一个表示位置的类.位置属于"客户.位置由 unicode 10 字符代码标识.位置代码"在特定客户的位置中应该是唯一的.
Let's say that I have a class that represents locations. Locations "belong" to customers. Locations are identified by a unicode 10 character code. The "location code" should be unique among the locations for a specific customer.
The two below fields in combination should be unique
customer_id = Column(Integer,ForeignKey('customers.customer_id')
location_code = Column(Unicode(10))
因此,如果我有两个客户,客户123"和客户456".它们都可以有一个名为main"的位置,但都不能有两个名为 main 的位置.
So if i have two customers, customer "123" and customer "456". They both can have a location called "main" but neither could have two locations called main.
我可以在业务逻辑中处理这个问题,但我想确保没有办法在 sqlalchemy 中轻松添加需求.unique=True 选项似乎仅在应用于特定字段时才有效,并且会导致整个表只有所有位置的唯一代码.
I can handle this in the business logic but I want to make sure there is no way to easily add the requirement in sqlalchemy. The unique=True option seems to only work when applied to a specific field and it would cause the entire table to only have a unique code for all locations.
推荐答案
摘自 文档:
Extract from the documentation of the Column
:
unique – 当为 True 时,表示该列包含一个唯一的约束,或者如果 index 也为 True,则表示索引应该使用唯一标志创建.在中指定多列约束/索引或指定显式名称,使用UniqueConstraint 或 索引显式构造.
unique – When True, indicates that this column contains a unique constraint, or if index is True as well, indicates that the Index should be created with the unique flag. To specify multiple columns in the constraint/index or to specify an explicit name, use the UniqueConstraint or Index constructs explicitly.
由于这些属于表而不属于映射的类,因此可以在表定义中声明它们,或者如果在 __table_args__
中使用声明式:
As these belong to a Table and not to a mapped Class, one declares those in the table definition, or if using declarative as in the __table_args__
:
# version1: table definition
mytable = Table('mytable', meta,
# ...
Column('customer_id', Integer, ForeignKey('customers.customer_id')),
Column('location_code', Unicode(10)),
UniqueConstraint('customer_id', 'location_code', name='uix_1')
)
# or the index, which will ensure uniqueness as well
Index('myindex', mytable.c.customer_id, mytable.c.location_code, unique=True)
# version2: declarative
class Location(Base):
__tablename__ = 'locations'
id = Column(Integer, primary_key = True)
customer_id = Column(Integer, ForeignKey('customers.customer_id'), nullable=False)
location_code = Column(Unicode(10), nullable=False)
__table_args__ = (UniqueConstraint('customer_id', 'location_code', name='_customer_location_uc'),
)
这篇关于sqlalchemy 在多列中是唯一的的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!