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 UserNumber field.
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 UserNumber field.
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.
The proxied_association.py from the SQLAlchemy examples should be a good place to start.