1

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?

talz
  • 1,004
  • 9
  • 22
  • A check constraint for this will be `check=Q(owner__isnull=False)` – Charnel Mar 23 '21 at 12:08
  • @Charnel wouldn't that just forbid `null` values from `owner`? – talz Mar 23 '21 at 12:09
  • You're right, the check is wrong. What you need is kind of `wildcard` for constaint check, but unfortunatly idk the way of doing this with ORM "out of the box". – Charnel Mar 23 '21 at 12:22

1 Answers1

2

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)
ruddra
  • 50,746
  • 7
  • 78
  • 101
  • No, this is not a solution, since this allows two devices with the same name, if in on of them `owner` is `null` and in the other one it's not `null`. So this allows a private device and a common device to have the same name, which should not be allowed. – talz Mar 23 '21 at 14:05
  • 1
    if you don't want to allow two devices with same name, then just add `unique=True` on name field. – ruddra Mar 23 '21 at 14:08
  • But as I've written in the question, I want to allow the same name if they have different, non-null owners "allow for two household members to name their private devices the same". – talz Mar 23 '21 at 14:11
  • in that case, it is probably better to handle it from python, not from DB side. – ruddra Mar 23 '21 at 15:15