Reputation: 55816
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 ?
Upvotes: 34
Views: 94429
Reputation: 7168
For people that, like me, came here to just find a working snippet to set unique contraint:
from sqlalchemy.orm import declarative_base
from sqlalchemy import Column, Integer, UniqueConstraint
Base = declarative_base()
class Model(Base):
__tablename__ = "model"
model_id = Column(Integer, primary_key=True)
# will create "model_num_key" UNIQUE CONSTRAINT, btree (num)
num = Column(Integer, unique=True)
# same with UniqueConstraint:
num2 = Column(Integer)
__table_args__ = (UniqueConstraint("num2", name="model_num2_key"),)
# for multiple columns:
# __table_args__ = (UniqueConstraint("num", "num2", name="two_columns"),)
Upvotes: 39
Reputation: 9509
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.
Upvotes: 19
Reputation: 5022
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.
Upvotes: 10