如何在SQLAlchemy中建立一个`UNIQUE`约束？ [英] How to model a `UNIQUE` constraint in SQLAlchemy?

问题描述

我正在写一个Flask / SQLAlchemy应用程序，其中包含用户和组。

I am writing a Flask/SQLAlchemy application in which I have users and groups.

用户可以属于多个组，在每个组内。 询问如何建模数据库我建议对我的多对多关系使用下面的表结构：

Users can belong to several groups, and they have a unique number within each group. Asking about how to model the database I was advised to use the following table structure for my many-to-many relationship:

TABLE UserGroups
  GroupID 
  UserID
  UserNumber
  PRIMARY KEY (GroupID, UserID)
  UNIQUE (GroupID, UserNumber)
  FOREIGN KEY (GroupID)
    REFERENCES Groups (GroupID)
  FOREIGN KEY (UserID)
    REFERENCES Users (UserID)

现在我知道如何使用SQLAlchemy创建一个常规的多对多关系，但我不知道如何表示 UNIQUE 约束与额外的 UserNumber 字段。

Now I know how to create a regular many-to-many relationship with SQLAlchemy, but I don't know how to represent the UNIQUE constraint with the additional UserNumber field.

我没有很多经验的数据库设计，ORMs和SQLAlchemy ，所以这可能是显而易见的，但我找不到一种方式来表达它。

I don't have a lot of experience with database design, ORMs and SQLAlchemy, so this may be obvious, but I can't find a way to express it.

我没有得到的东西是：我的 User 类有一个 list-like 属性 groups 所有他所属的组，但这完全隐藏 UserGroups 加入表，我不知道如何访问 UserNumber 字段。

On of the things I don't get is: using a regular many-to-many relationship, my User class has a list-like attribute groups which contains all the groups he belongs to, but this completely hides the UserGroups joining-table and I don't know how to access the UserNumber field.

这对我来说有点模糊。关于如何用SQLAlchemy做这样的事情你有什么好的例子或解释？

This is all a bit blur to me. Do you have any good example or explanations on how-to do such a thing with SQLAlchemy ?

推荐答案

问题的第一部分（关于使用多个列创建唯一约束）已经由cleg 回答。

The first part of the question (about creating a unique constraint with multiple columns) is already answered by cleg.

然而，如果您想在映射表中有额外的列，则默认的多对多方法不起作用。相反，您应该使用关联对象模式。此外，您可以使用 association_proxy 。

However, the default many-to-many approach doesn't work if you want to have additionally columns in the mapping table. Instead, you should use the Association Object Pattern. Additionally, you can simplify the access between user and group with an association_proxy.

orm / examples.html＃associationsrel =nofollow> SQLAlchemy示例应该是一个很好的开始。

The proxied_association.py from the SQLAlchemy examples should be a good place to start.

这篇关于如何在SQLAlchemy中建立一个`UNIQUE`约束？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！