CODEWITHSUNDEEP
pythonmysqldjangomodel
Neil
Neil

Reputation: 3291

Django/MySQL Unique Constraint how to treat NULLs as equal

I have a Django model which has a recursive field. A simplified version is below. The idea is roughly to have a tree data structure in sql. The problem which I have is that, apparently, Django does not treat NULLs as equal. The problem now is that, since every tree's root has a 'null pointer' by necessity, I can have two identical trees but Django will treat them as different because of the NULL value. How can I implement the UniqueConstraint below so that two 'Link' objects with NULL link values and equal node values will be treated as identical, and fail the UniqueConstraint test? Thank you.

class Link(models.Model):
    node = models.ForeignKey(Node, on_delete=models.CASCADE) 

    link = models.ForeignKey('self', on_delete = models.CASCADE, null=True)


    class Meta:

        constraints = [
                models.UniqueConstraint(['node', 'link'], name='pipe_unique')
                ]

EDIT Of course ideally the constraint would be enforced by the db. But even if I can enforce it in application logic by hooking somewhere or using a custom constraint, that would be good enough.

Upvotes: 0

Views: 454

Answers (1)

Iain Shelvington
Iain Shelvington

Reputation: 32274

You may be able to do this with a custom constraint

UniqueConstraint(fields=['node'], condition=Q(link__isnull=True), name='unique_root_node')

EDIT:

If you wished to manually add the check you could do it in the save method of Link and also in the clean method so that it get's run in any model forms before you even get to saving the instance

def clean(self):
    if self.node_id and not self.link_id:
        if self.__class__.objects.exclude(pk=self.pk).filter(node=self.node, link__isnull=True).exists():
            raise ValidationError(f'A root node already exists for {self.node}')

Excluding pk=self.pk avoids getting conflicts with itself when updating an object

def save(self, *args, **kwargs):
    self.clean()
    super().save(*args, **kwargs)

Upvotes: 1

Related Questions