Duplicate Records created when specifying None as a target in a custom ManyToManyField

[Credentive]

Reading through the documents, the only reference to null in ManyToManyFields is the following:

null has no effect since there is no way to require a relationship at the database level.

When allowing null values as a target in an M2M field, you are allowed to assign "None" as the target of a model. However, if you assign "None" multiple times, you will get multiple DB records.

Understanding that M2M fields are implemented as join tables, I can see why this may be happening, but I think this behavior should be documented at least. Note from the example that adding non-null targets multiple times produces the expected result (it works, but no extra rows are created)

Model code (<ProjectRoot>/policypublisher/models.py):

class Section(models.Model):
    uuid = models.UUIDField(
        primary_key=True,
        unique=True,
        editable=False,
        default=uuid.uuid4,
        help_text="A unique identifier for the Section",
    )
    <...>
    version = models.ManyToManyField(Version, related_name="sections_in_version")
    <...>
    under = models.ManyToManyField("self", through="SectionHierarchy", symmetrical=False, related_name="over")

class SectionHierarchy(models.Model):
    under_id = models.ForeignKey(Section, on_delete=models.CASCADE, related_name="+")
    over_id = models.ForeignKey(Section, null=True, on_delete=models.CASCADE, related_name="+")
    version = models.ForeignKey(Version, on_delete=models.CASCADE)

    class Meta:
        constraints = [
            models.UniqueConstraint(fields=["under_id", "over_id", "version"], name="unique_sec_under_per_version")
        ]

$ python manage.py shell

Python 3.9.15 (main, Nov 15 2022, 09:54:34) 
[GCC 10.2.1 20210110] on linux
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from policypublisher.models import *
>>> section = Section.objects.first()
>>> version = section.version.first()
>>> SectionHierarchy.objects.count()
0
>>> section.under.add(None, through_defaults={"version": version})
>>> section.under.add(None, through_defaults={"version": version})
>>> SectionHierarchy.objects.count()
2
>>> section2 = Section.objects.last()
>>> section.under.add(section2, through_defaults={"version":version})
>>> SectionHierarchy.objects.count()
3
>>> section.under.add(section2, through_defaults={"version":version})
>>> SectionHierarchy.objects.count()
3
>>> 

$ python manage.py dbshell

SQLite version 3.34.1 2021-01-20 14:10:07
Enter ".help" for usage hints.
sqlite> select * from policypublisher_sectionhierarchy;
55||055744fc79e24f89a9384d79894eee7a|be8225c85fab4a0ba8ea879a3d992abe
56||055744fc79e24f89a9384d79894eee7a|be8225c85fab4a0ba8ea879a3d992abe

[Mariusz Felisiak]

When allowing null values as a target in an M2M field, you are allowed to assign "None" as the target of a model. However, if you assign "None" multiple times, you will get multiple DB records.
Understanding that M2M fields are implemented as join tables, I can see why this may be happening, but I think this behavior should be documented at least. Note from the example that adding non-null targets multiple times produces the expected result (it works, but no extra rows are created)

NULL doesn't violate unique constraints or cause conflicts in most (all?) databases, that's why it's possible to add multiple NULL-relations.

... this behavior should be documented at least.

This is a database behavior, not something that Django is responsible for. I'm not sure Django documentation is the right place to document this. Closing as "invalid" unless someone strongly believes we should document this.