[Django] #33209: ManyToManyField.add() doesn't respect a unique constraint in intermediate table

13 views
Skip to first unread message

Django

unread,
Oct 19, 2021, 9:34:34 PM10/19/21
to django-...@googlegroups.com
#33209: ManyToManyField.add() doesn't respect a unique constraint in intermediate
table
-------------------------------------+-------------------------------------
Reporter: okapies | Owner: nobody
Type: Bug | Status: new
Component: Database | Version: 3.1
layer (models, ORM) |
Severity: Normal | Keywords:
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
I added the following `Company` and `Member` models, and `CompanyMember`
as their intermediate table with a unique constraint including `role`
field in addition to `through_fields`.

{{{#!python
from django.db import models

class Company(models.Model):
pass

class Member(models.Model):
companies = models.ManyToManyField(Company, through='CompanyMember',
related_name='members')

class CompanyMember:
company = models.ForeignKey(Company, on_delete=models.CASCADE)
member = models.ForeignKey(Member, on_delete=models.CASCADE)
role = models.SmallIntegerField()

class Meta:
constraints = [
models.UniqueConstraint(fields=['company', 'member', 'role'],
name='company_member_role'),
]
}}}

In this situation, `company.members.add()` fails to add existing member
with different role.

{{{#!python
company = Company.objects.create()
member = Member.objects.create()

company.members.add(member, through_defaults={'role': 1})
assert Company.objects.all().count() == 1

company.members.add(member, through_defaults={'role': 2})
assert Company.objects.all().count() == 2 # fails
}}}

We need to workaround by adding the relation to the intermediate table
directly.

{{{#!python
company.members.through.objects.create(
company.members.through(company=company, member=member, role=2)
)
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33209>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

Django

unread,
Oct 19, 2021, 9:36:17 PM10/19/21
to django-...@googlegroups.com
#33209: ManyToManyField.add() doesn't respect a unique constraint in intermediate
table
-------------------------------------+-------------------------------------
Reporter: okapies | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by okapies:

Old description:

New description:

I added the following `Company` and `Member` models, and `CompanyMember`
as their intermediate table with a unique constraint including `role`
field in addition to `through_fields`.

{{{#!python
from django.db import models

class Company(models.Model):
pass

class Member(models.Model):
companies = models.ManyToManyField(Company, through='CompanyMember',
related_name='members')

class CompanyMember:
company = models.ForeignKey(Company, on_delete=models.CASCADE)
member = models.ForeignKey(Member, on_delete=models.CASCADE)
role = models.SmallIntegerField()

class Meta:
constraints = [
models.UniqueConstraint(fields=['company', 'member', 'role'],
name='company_member_role'),
]
}}}

In this situation, `company.members.add()` fails to add existing member

with different role specified via `through_defaults`.

{{{#!python
company = Company.objects.create()
member = Member.objects.create()

company.members.add(member, through_defaults={'role': 1})
assert Company.objects.all().count() == 1

company.members.add(member, through_defaults={'role': 2})
assert Company.objects.all().count() == 2 # fails
}}}

We need to workaround by adding the relation to the intermediate table
directly.

{{{#!python
company.members.through.objects.create(
company.members.through(company=company, member=member, role=2)
)
}}}

--

--
Ticket URL: <https://code.djangoproject.com/ticket/33209#comment:1>

Django

unread,
Oct 19, 2021, 9:37:28 PM10/19/21
to django-...@googlegroups.com
#33209: ManyToManyField.add() doesn't respect a unique constraint in intermediate
table
-------------------------------------+-------------------------------------
Reporter: okapies | Owner: nobody
Type: Bug | Status: new
Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by okapies:

Old description:

> I added the following `Company` and `Member` models, and `CompanyMember`


> as their intermediate table with a unique constraint including `role`
> field in addition to `through_fields`.
>
> {{{#!python
> from django.db import models
>
> class Company(models.Model):
> pass
>
> class Member(models.Model):
> companies = models.ManyToManyField(Company, through='CompanyMember',
> related_name='members')
>
> class CompanyMember:
> company = models.ForeignKey(Company, on_delete=models.CASCADE)
> member = models.ForeignKey(Member, on_delete=models.CASCADE)
> role = models.SmallIntegerField()
>
> class Meta:
> constraints = [
> models.UniqueConstraint(fields=['company', 'member', 'role'],
> name='company_member_role'),
> ]
> }}}
>
> In this situation, `company.members.add()` fails to add existing member

> with different role specified via `through_defaults`.
>

> {{{#!python
> company = Company.objects.create()
> member = Member.objects.create()
>
> company.members.add(member, through_defaults={'role': 1})
> assert Company.objects.all().count() == 1
>
> company.members.add(member, through_defaults={'role': 2})
> assert Company.objects.all().count() == 2 # fails
> }}}
>
> We need to workaround by adding the relation to the intermediate table
> directly.
>
> {{{#!python
> company.members.through.objects.create(
> company.members.through(company=company, member=member, role=2)
> )
> }}}

New description:

I added the following `Company` and `Member` models, and `CompanyMember`
as their intermediate table with a unique constraint including `role`
field in addition to `through_fields`.

{{{#!python
from django.db import models

class Company(models.Model):
pass

class Member(models.Model):
companies = models.ManyToManyField(Company, through='CompanyMember',
related_name='members')

class CompanyMember:
company = models.ForeignKey(Company, on_delete=models.CASCADE)
member = models.ForeignKey(Member, on_delete=models.CASCADE)
role = models.SmallIntegerField()

class Meta:
constraints = [
models.UniqueConstraint(fields=['company', 'member', 'role'],
name='company_member_role'),
]
}}}

In this situation, `company.members.add()` fails to add existing member

with different role specified via `through_defaults`.

{{{#!python


company = Company.objects.create()
member = Member.objects.create()

company.members.add(member, through_defaults={'role': 1})

assert company.members.through.objects.all().count() == 1

company.members.add(member, through_defaults={'role': 2})

assert company.members.through.objects.all().count() == 2 # fails
}}}

We need to workaround by adding the relation to the intermediate table
directly.

{{{#!python
company.members.through.objects.create(
company.members.through(company=company, member=member, role=2)
)
}}}

--

--
Ticket URL: <https://code.djangoproject.com/ticket/33209#comment:2>

Django

unread,
Oct 19, 2021, 9:38:05 PM10/19/21
to django-...@googlegroups.com
#33209: ManyToManyField.add() doesn't respect a unique constraint in intermediate
table
-------------------------------------+-------------------------------------
Reporter: Yuta Okamoto | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Yuta Okamoto:

Old description:

> I added the following `Company` and `Member` models, and `CompanyMember`
> as their intermediate table with a unique constraint including `role`
> field in addition to `through_fields`.
>
> {{{#!python
> from django.db import models
>
> class Company(models.Model):
> pass
>
> class Member(models.Model):
> companies = models.ManyToManyField(Company, through='CompanyMember',
> related_name='members')
>
> class CompanyMember:
> company = models.ForeignKey(Company, on_delete=models.CASCADE)
> member = models.ForeignKey(Member, on_delete=models.CASCADE)
> role = models.SmallIntegerField()
>
> class Meta:
> constraints = [
> models.UniqueConstraint(fields=['company', 'member', 'role'],
> name='company_member_role'),
> ]
> }}}
>
> In this situation, `company.members.add()` fails to add existing member

> with different role specified via `through_defaults`.
>

> {{{#!python
> company = Company.objects.create()
> member = Member.objects.create()
>
> company.members.add(member, through_defaults={'role': 1})

> assert company.members.through.objects.all().count() == 1


>
> company.members.add(member, through_defaults={'role': 2})

> assert company.members.through.objects.all().count() == 2 # fails


> }}}
>
> We need to workaround by adding the relation to the intermediate table
> directly.
>
> {{{#!python
> company.members.through.objects.create(
> company.members.through(company=company, member=member, role=2)
> )
> }}}

New description:

I added the following `Company` and `Member` models, and `CompanyMember`
as their intermediate table with a unique constraint including `role`
field in addition to `through_fields`.

{{{#!python
from django.db import models

class Company(models.Model):
pass

class Member(models.Model):
companies = models.ManyToManyField(Company, through='CompanyMember',
related_name='members')

class CompanyMember:
company = models.ForeignKey(Company, on_delete=models.CASCADE)
member = models.ForeignKey(Member, on_delete=models.CASCADE)
role = models.SmallIntegerField()

class Meta:
constraints = [
models.UniqueConstraint(fields=['company', 'member', 'role'],
name='company_member_role'),
]
}}}

In this situation, `company.members.add()` silently fails to add existing
member with different role specified via `through_defaults`.

{{{#!python
company = Company.objects.create()
member = Member.objects.create()

company.members.add(member, through_defaults={'role': 1})

assert company.members.through.objects.all().count() == 1

company.members.add(member, through_defaults={'role': 2})

assert company.members.through.objects.all().count() == 2 # fails
}}}

We need to workaround by adding the relation to the intermediate table
directly.

{{{#!python
company.members.through.objects.create(
company.members.through(company=company, member=member, role=2)
)
}}}

--

--
Ticket URL: <https://code.djangoproject.com/ticket/33209#comment:3>

Django

unread,
Oct 19, 2021, 9:42:43 PM10/19/21
to django-...@googlegroups.com
#33209: ManyToManyField.add() doesn't respect a unique constraint in intermediate
table
-------------------------------------+-------------------------------------
Reporter: Yuta Okamoto | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Yuta Okamoto:

Old description:

> I added the following `Company` and `Member` models, and `CompanyMember`


> as their intermediate table with a unique constraint including `role`
> field in addition to `through_fields`.
>
> {{{#!python
> from django.db import models
>
> class Company(models.Model):
> pass
>
> class Member(models.Model):
> companies = models.ManyToManyField(Company, through='CompanyMember',
> related_name='members')
>
> class CompanyMember:
> company = models.ForeignKey(Company, on_delete=models.CASCADE)
> member = models.ForeignKey(Member, on_delete=models.CASCADE)
> role = models.SmallIntegerField()
>
> class Meta:
> constraints = [
> models.UniqueConstraint(fields=['company', 'member', 'role'],
> name='company_member_role'),
> ]
> }}}
>

> In this situation, `company.members.add()` silently fails to add existing
> member with different role specified via `through_defaults`.


>
> {{{#!python
> company = Company.objects.create()
> member = Member.objects.create()
>
> company.members.add(member, through_defaults={'role': 1})

> assert company.members.through.objects.all().count() == 1


>
> company.members.add(member, through_defaults={'role': 2})

> assert company.members.through.objects.all().count() == 2 # fails


> }}}
>
> We need to workaround by adding the relation to the intermediate table
> directly.
>
> {{{#!python
> company.members.through.objects.create(
> company.members.through(company=company, member=member, role=2)
> )
> }}}

New description:

I added the following `Company` and `Member` models, and `CompanyMember`
as their intermediate table with a unique constraint including `role`
field in addition to `through_fields`.

{{{#!python
from django.db import models

class Company(models.Model):
pass

class Member(models.Model):
companies = models.ManyToManyField(Company, through='CompanyMember',

through_fields=('company', 'member'), related_name='members')

class CompanyMember:
company = models.ForeignKey(Company, on_delete=models.CASCADE)
member = models.ForeignKey(Member, on_delete=models.CASCADE)
role = models.SmallIntegerField()

class Meta:
constraints = [
models.UniqueConstraint(fields=['company', 'member', 'role'],
name='company_member_role'),
]
}}}

In this situation, `company.members.add()` silently fails to add existing
member with different role specified via `through_defaults`.

{{{#!python
company = Company.objects.create()
member = Member.objects.create()

company.members.add(member, through_defaults={'role': 1})

assert company.members.through.objects.all().count() == 1

company.members.add(member, through_defaults={'role': 2})

assert company.members.through.objects.all().count() == 2 # fails
}}}

We need to workaround by adding the relation to the intermediate table
directly.

{{{#!python
company.members.through.objects.create(
company.members.through(company=company, member=member, role=2)
)
}}}

--

--
Ticket URL: <https://code.djangoproject.com/ticket/33209#comment:4>

Django

unread,
Oct 19, 2021, 9:53:07 PM10/19/21
to django-...@googlegroups.com
#33209: ManyToManyField.add() doesn't respect a unique constraint in intermediate
table
-------------------------------------+-------------------------------------
Reporter: Yuta Okamoto | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Description changed by Yuta Okamoto:

Old description:

> I added the following `Company` and `Member` models, and `CompanyMember`


> as their intermediate table with a unique constraint including `role`
> field in addition to `through_fields`.
>
> {{{#!python
> from django.db import models
>
> class Company(models.Model):
> pass
>
> class Member(models.Model):
> companies = models.ManyToManyField(Company, through='CompanyMember',

> through_fields=('company', 'member'), related_name='members')


>
> class CompanyMember:
> company = models.ForeignKey(Company, on_delete=models.CASCADE)
> member = models.ForeignKey(Member, on_delete=models.CASCADE)
> role = models.SmallIntegerField()
>
> class Meta:
> constraints = [
> models.UniqueConstraint(fields=['company', 'member', 'role'],
> name='company_member_role'),
> ]
> }}}
>

> In this situation, `company.members.add()` silently fails to add existing
> member with different role specified via `through_defaults`.
>

> {{{#!python
> company = Company.objects.create()
> member = Member.objects.create()
>
> company.members.add(member, through_defaults={'role': 1})

> assert company.members.through.objects.all().count() == 1


>
> company.members.add(member, through_defaults={'role': 2})

> assert company.members.through.objects.all().count() == 2 # fails


> }}}
>
> We need to workaround by adding the relation to the intermediate table
> directly.
>
> {{{#!python
> company.members.through.objects.create(
> company.members.through(company=company, member=member, role=2)
> )
> }}}

New description:

I added the following `Company` and `Member` models, and `CompanyMember`
as their intermediate table with a unique constraint including `role`
field in addition to `through_fields`.

{{{#!python
from django.db import models

class Company(models.Model):
pass

class Member(models.Model):
companies = models.ManyToManyField(Company, through='CompanyMember',

through_fields=('company', 'member'), related_name='members')

class CompanyMember:
company = models.ForeignKey(Company, on_delete=models.CASCADE)
member = models.ForeignKey(Member, on_delete=models.CASCADE)
role = models.SmallIntegerField()

class Meta:
constraints = [
models.UniqueConstraint(fields=['company', 'member', 'role'],
name='company_member_role'),
]
}}}

In this situation, `company.members.add()` silently fails to add existing


member with different role specified via `through_defaults`.

{{{#!python


company = Company.objects.create()
member = Member.objects.create()

company.members.add(member, through_defaults={'role': 1})

assert company.members.through.objects.all().count() == 1

company.members.add(member, through_defaults={'role': 2})

assert company.members.through.objects.all().count() == 2 # fails
}}}

We need to workaround by adding the relation to the intermediate table
directly.

{{{#!python
company.members.through.objects.create(company=company, member=member,
role=2)
}}}

--

--
Ticket URL: <https://code.djangoproject.com/ticket/33209#comment:5>

Django

unread,
Oct 19, 2021, 11:43:52 PM10/19/21
to django-...@googlegroups.com
#33209: ManyToManyField.add() doesn't respect a unique constraint in intermediate
table
-------------------------------------+-------------------------------------
Reporter: Yuta Okamoto | Owner: nobody
Type: Bug | Status: new

Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Simon Charette):

I'll let others chime in but I think this is invalid.

`ManyToManyField` was designed to allow only a single instance of the
relationship it defines and not allow extra dimensions to be considered.
In your case that means a single instance of the `Member <-> Company`
many-to-many relationship can be tracked at a time and the `role`
dimension is not taken into account at all.

If you want to keep using `ManyToManyField` for this purpose you'll likely
need to tweak your data model a bit

e.g.

{{{#!python
class Company(models.Model):
pass

class Role(models.Model):
company = models.ForeignKey(Company, related_name='roles')

class Member(models.Model):
roles = models.ManyToManyField(Role, related_name='members')

@property
def companies(self):
return Company.objects.filter(roles__members=self)
}}}

--
Ticket URL: <https://code.djangoproject.com/ticket/33209#comment:6>

Django

unread,
Oct 20, 2021, 1:31:54 AM10/20/21
to django-...@googlegroups.com
#33209: ManyToManyField.add() doesn't respect a unique constraint in intermediate
table
-------------------------------------+-------------------------------------
Reporter: Yuta Okamoto | Owner: nobody
Type: Bug | Status: closed

Component: Database layer | Version: 3.1
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Mariusz Felisiak):

* status: new => closed
* resolution: => invalid


Comment:

I agree with Simon. As documented ''"Using add() on a relation that
already exists won’t duplicate the relation,..."'' and in your case
`member` and `company` are already related.

--
Ticket URL: <https://code.djangoproject.com/ticket/33209#comment:7>

Reply all
Reply to author
Forward
0 new messages