Menu
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 ?
674
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.
To create a composite primary key, set primary_key to True on each column involved in the key. A boolean argument when set to False adds NOT NULL constraint while creating a column. Its default value is True .
A foreign key in SQL is a table-level construct that constrains one or more columns in that table to only allow values that are present in a different set of columns, typically but not always located on a different table.
¶ The SQLAlchemy ORM, in order to map to a particular table, needs there to be at least one column denoted as a primary key column; multiple-column, i.e. composite, primary keys are of course entirely feasible as well.
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.
155
Use 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.
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
