How to model a `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 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 ?

Will someone edit the question title to reflect what's being asked more specifically? — Frank Smith, Dec 11 '22 at 07:29

score 17 · Accepted Answer · edited May 23 '17 at 11:46

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.

score 10 · Answer 2 · edited May 23 '17 at 10:30

10

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.

edited May 23 '17 at 10:30

Community

1
1

answered Jan 16 '13 at 09:57

cleg

4,862
5
35
52

4

The default many-to-many approach doesn't work if you want to have additionally columns in the mapping table. – schlamar Jan 16 '13 at 11:27
I wrote my answer before seen @schlamar, but now added a reference. – cleg Jan 16 '13 at 13:13
It's not the matter of repo, just want to be honest :) – cleg Jan 16 '13 at 13:23

kosciej16 · Answer 3 · 2023-05-01T23:00:03.317

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"),)

How to model a `UNIQUE` constraint in SQLAlchemy?

3 Answers3

Linked