So I must be missing something, but here's what I have right now:
tournament_table = Table(
'tournament',
Base.metadata,
Column('id', UUID(as_uuid=True), primary_key=True))
team_table = Table(
'team',
Base.metadata,
Column('id', UUID(as_uuid=True), primary_key=True,
* Column('tournament_id', UUID(as_uuid=True), ForeignKey('tournament.id
<http://tournament.id>'), nullable=False),*
Column('display_name', String(length=255), nullable=False),
UniqueConstraint('tournament_id', 'display_name'))
team_person_table = Table(
'team_person',
Base.metadata,
*Column('team_id', UUID(as_uuid=True), ForeignKey('team.id
<http://team.id>'), primary_key=True*),
*Column('person_id', UUID(as_uuid=True), ForeignKey('person.id
<http://person.id>'), primary_key=True)*)
person_table = Table(
'person',
Base.metadata,
Column('id', UUID(as_uuid=True), primary_key=True))
In my current model I can create
Tournament A, Team A (paired to Tournament A), Team B (also paired to
Tournament A), and Team C (also paired to Tournament A)
I can then assign *Person Z* to Team A, Team B and Team C separately
without throwing an integrity exception.
How can I make it so that doesn't happen?
On Wed, Sep 4, 2019 at 4:53 PM Derek Lambert <[email protected]>
wrote:
> If I'm understanding correctly...
>
> You're on the right track. I'd use a composite primary key on
> |team_person|, consisting of foreign keys from |person| and |team|, and
> another composite key (or unique index) on the |team| to |tournament|
> table. This lets the database do all the work.
>
> -Derek
>
> On Wednesday, September 4, 2019 at 4:16:02 PM UTC-5, 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.
>>
>> 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/d93a10e6-cae8-4346-bae4-99782546becf%40googlegroups.com
> <https://groups.google.com/d/msgid/sqlalchemy/d93a10e6-cae8-4346-bae4-99782546becf%40googlegroups.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/CAHmCLHrH0kCuYReaQgCZpAy4T9HkJxV_CFv5wKbTWJDr-x6nUg%40mail.gmail.com.
