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)
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
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.
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
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 ?
The first part of the question (about creating a unique constraint with multiple columns) is already answered by cleg.
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.
UniqueConstraint in your model. In detailed it's described in this question: sqlalchemy unique across multiple columns
As for many-to-many relations, SQLAlchemy have pretty good tutorial.
P.S. Sorry, I've missed with second part of answer (it's more complex then I've thought, so see answer from @schlamar), but first part is still correct.