unique_together is not working with self referencing foreign key?

Question

class Category(models.Model):
    name = models.CharField(max_length=200)
    parent = models.ForeignKey("self",
                               blank=True,
                               null=True,
                               related_name='children',
                               on_delete=models.CASCADE)

    class Meta:
        unique_together = [
            ('parent', 'name'),
        ]

In this model I am able to create multiple objects with

Category.objects.create(name="cat1", parent=None) # 
Category.objects.create(name="cat1", parent=None) 
# unique_together constraint should not 
# allow this second object's reaction, but it is; 
# behavior is the same even when the parent is not None.

Django version I am using is 3.0.8 Postgres 12.3 psycopg2-binary 2.8.5

Update:

Category.objects.create(name="cat1", parent=obj) # 
Category.objects.create(name="cat1", parent=obj)

second object or record is getting created even when parent is not None.

In [2]: Category2.objects.create(name="cat1")                            
Out[2]: <Category2: cat1>

In [3]: Category2.objects.create(name="cat1")                            
Out[3]: <Category2: cat1>

In [4]: par1 = Category2.objects.create(name="cat1")                     

In [6]: par1 = Category2.objects.create(name="cat2", parent=par1)        

In [7]: par2 = Category2.objects.create(name="cat2", parent=par1)

This question not a duplicate, it is not related to nullable foreign keys as explained in the question.

in my case the I am able create records even with not-null fields values — Javed, Jul 26 '20 at 22:06
Can you provide an example of the error occurring when non-null values are used? — Iain Shelvington, Jul 26 '20 at 22:09
javed: databases do not consider two `NULL`s to be the same. So `NULL` will not "breach" the uniqness constraint. — Willem Van Onsem, Jul 26 '20 at 22:11
@javed: exactly what database do you use? Not all databases enforce all checks. Some simply ignore `CHECK ...` clauses, etc. — Willem Van Onsem, Jul 26 '20 at 22:35
@WillemVanOnsem thanks you answer solves the problem, as explained, the issue is not just related to nullable foreign key. — Javed, Jul 27 '20 at 12:31

Willem Van Onsem · Accepted Answer · 2020-07-27T18:18:06.767

Databases will ignore NULL when checking uniqness, it thus means that two NULLs are not considered braching uniqness.

You can make use of the constraint framework to check uniqness conditionally:

from django.db.models import Q, UniqueConstraint

class Category(models.Model):
    name = models.CharField(max_length=200)
    parent = models.ForeignKey(
        'self',
        blank=True,
        null=True,
        related_name='children',
        on_delete=models.CASCADE
    )

    class Meta:
        constraints = [
            UniqueConstraint(
                fields=['name', 'parent'], name='name_unique'
            ),
            UniqueConstraint(
                fields=['name'], condition=Q(parent=None), name='name_unique2'
            )
        ]

Here if the parent is thus NULL, we check the uniqness of name. Some databases however might not enforce such checks.

Appartently on Microsoft's SQL Server, this is not the case, as @Melvyn says. In this case that would be wanted behavior. But often it is, in my humble opinion, unwanted behavior. NULL is often used exactly for this purpose.

One exception to the rule that NULL is not a value (but the absence of one) is Microsoft SQL Server. Makes working with nullable one to one fields a challenge. — , Jul 27 '20 at 12:48

unique_together is not working with self referencing foreign key?

1 Answers1