On Wed, Sep 4, 2019, at 5:12 PM, Michael P. McDonnell wrote: > Hey - > I'm again at a loss of what to google, and as this will ultimately need to be > represented in some fashion in sqlalchemy, I figured this is a great place to > start: > > I have a |person| table and a |team| table with a many to many table in > between |team_person|. > Simple enough! > > Now - to make it fun. > |team| has a relationship to |tournament| > How can I prevent a user from joining more than 1 team in a given tournament? > > I thought about adding a 3rd column to my M2M table, > (team_tournament_person), but that could still fail because it could be a > team from tournament x, tournament y's ID and a Person Q's ID.
if you make team_person have tournament_id, person_id, team_id, then team_person has (tournament_id, person_id) as primary key, that means person_id can only be part of a tournament_id once. Then you create foreign key on team_person for (team_id, tournament_id) to team_table(id, tournament_id). this might need you to create a unique constraint on team(id, tournament_id) or set that as its primary key. > > So any suggestions on what I should be googling, and then how to implement in > SA would be hugely appreciated! > > Thanks! > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [email protected]. > To view this discussion on the web visit > https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHq%2BCorBMogufyGh6jpX%2BBt6gNpT38oPe4TJ85xDoahHxQ%40mail.gmail.com > > <https://groups.google.com/d/msgid/sqlalchemy/CAHmCLHq%2BCorBMogufyGh6jpX%2BBt6gNpT38oPe4TJ85xDoahHxQ%40mail.gmail.com?utm_medium=email&utm_source=footer>. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/e644c7a1-c72e-42a3-b9dc-262775c46cf5%40www.fastmail.com.
