unique_together does not handle null as most would expect it

[Jimmy Merrild Krag]

If you are not well into databases, you may not realize that NULL is not the same as NULL, and therefore the below may seem like a good idea to ensure that you SpaceShip model can only have one currently active (not deleted) Status:

class SpaceShip(models.Model):
    ...

class Status(models.Model):
    paper = models.ForeignKey('SpaceShip', null=False, blank=False)

    created = models.DateTimeField(default=timezone.now, null=False, blank=False, editable=False)
    created_by = models.ForeignKey('auth.User', null=False, blank=False, editable=False)

    text = models.TextField(blank=False)

    deleted = models.DateTimeField(null=True, blank=True, editable=False)
    deleted_by = models.ForeignKey('auth.User', null=True, blank=True, editable=False)

    class Meta:
        ordering = ('created',)
        unique_together = ('paper', 'deleted')

People have handled this kind of scenario in the clean method of the model (most notably ​https://stackoverflow.com/a/4805581/1031534 which defines a super class for models where this is handled generally), but then you still have to override the save method to clean during save.

Perhaps Django should issue a warning when creating unique_together constraints, where one of the fields is null-able, or alternatively the constraint could be handled in Python.

[Aymeric Augustin]

I understand this behavior may be surprising but it's well established and widely relied upon.

I think "unique across non null values" is more generally useful than "at most one null value".

I'm -1 on raising a warning in that case. It would be a false positive for far too many people.

[Jimmy Merrild Krag]

I do however see the case for the current behaviour, but do you think there would be any chance that an option (per unique relationship) to change the behaviour would be accepted?

Not sure how to make it, but I sure would be useful.