title: How to model a `UNIQUE` constraint in SQLAlchemy?
body: <p>I am writing a Flask/SQLAlchemy application in which I have users and groups.</p>
  
  <p>Users can belong to several groups, and <strong>they have a unique number within each group</strong>. <a href="https://dba.stackexchange.com/questions/32961/how-to-model-a-user-group-relationship-where-users-are-assigned-an-unique-number/32965#32965">Asking about how to model the database</a> I was advised to use the following table structure for my many-to-many relationship:</p>
  
  <pre><code>TABLE UserGroups
    GroupID 
    UserID
    UserNumber
    PRIMARY KEY (GroupID, UserID)
    UNIQUE (GroupID, UserNumber)
    FOREIGN KEY (GroupID)
      REFERENCES Groups (GroupID)
    FOREIGN KEY (UserID)
      REFERENCES Users (UserID)
  </code></pre>
  
  <p>Now I know how to create a regular many-to-many relationship with SQLAlchemy, but I don't know how to represent the <code>UNIQUE</code> constraint with the additional <code>UserNumber</code> field.</p>
  
  <p>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.</p>
  
  <p>On of the things I don't get is: using a regular many-to-many relationship, my <code>User</code> class has a <em>list-like</em> attribute <code>groups</code> which contains all the groups he belongs to, but this completely hides the <code>UserGroups</code> joining-table and I don't know how to access the <code>UserNumber</code> field.</p>
  
  <p>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 ?</p>