Django: How to treat null as equal to everything for uniqueness constraints?

Question

In the title I mean equal to any value, not just to other nulls.

Let's say for the sake of the example that we have a household, with household members (people) and electronic devices. Some electronic devices are personal and belong to one person, but some do not belong to any one household member, like for example a common computer in the study. We might then model electronic devices like this:

class ElectronicDevice(models.Model):
    name = models.CharField(null=False, max_length=64)
    owner = models.ForeignKey(HouseMember, null=True, on_delete=models.CASCADE)

Now let's say we want device names to be unique from the perspective of house members. A house member should not have two devices available for them which have the same name. So name and owner should be unique together, but there should also not be two devices, one private and one common (owner is null) which have the same name.

Thus a normal UniqueConstraint:

class Meta:
        constraints = [
            models.UniqueConstraint(fields=['name', 'owner'], name='uniqe_device_name'),
        ]

would not do, since it still allows two devices to have the same name if for one of them owner is null. Only constraining the name field:

class Meta:
        constraints = [
            models.UniqueConstraint(fields=['name', ], name='uniqe_device_name'),
        ]

also would not do, because this would not allow for two household members to name their private devices the same, though we do want to allow that.

My current attempt is to constraint uniqueness of name and owner, and then with a CheckConstraint not allow a name if there is already a common device (owner is null) with that name:

class Meta:
        constraints = [
            models.UniqueConstraint(fields=['name', 'owner'], name='uniqe_device_name'),
            models.CheckConstraint(check=???, name='no_common_device_with_same_name')
        ]

Is this the best approach here or is there a better solution? If this is the best solution: how to write the CheckConstraint for that?

Answer 1

Probably you can try like this:

constraints = [ 
    models.UniqueConstraint(fields=['name', 'owner'], condition = Q(owner__isnull=False), name='uniqe_device_name'),
    models.UniqueConstraint(fields=['name'], condition = Q(owner__isnull=True), name='uniqe_device_name_without_owner')
]

More infromation can be found in documentation.

Update

If you need custom conditions which can't be handled from Constraints, then it is better to override the save method. For example:

class ElectronicDevice(models.Model):
    ...
    def save(self, *args, **kwargs):
        if some conditions:
           raise ValidationError()
        super().save(*args, **kwargs)