How to simulate the `UNIQUE` constraint in SQLAlchemy?

Question

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

Users can belong to several groups, and have a unique number in each group . A query on how to model a database I was recommended 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) 

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

I don't have much experience developing databases, ORMs, or SQLAlchemy, so this may be obvious, but I cannot find a way to express it.

For things that I don't get, the following is used: using a regular many-to-many relationship, my User class has an attribute on the groups list that contains all the groups it belongs to, but this completely hides the UserGroups connection table, and I do not know how to access the UserNumber field.

It all looks a bit like me. Do you have a good example or explanation on how to do this with SQLAlchemy?

Answer 1

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

However, the default many-to-many approach does not work if you want to have additional columns in the mapping table. Instead, you should use an association object template . In addition, you can simplify access between the user and the group using association_proxy .

proxied_association.py of SQLAlchemy Examples should be a good place to start.

Answer 2

Use UniqueConstraint in your model. In the detailed description of this question: sqlalchemy is unique to multiple columns

As with many-to-many relationships, SQLAlchemy has a pretty good tutorial .

PS Sorry, I missed the second part of the answer (this is harder than I thought, so see the answer from @schlamar), but the first part is still correct.