How to enforce uniqueness with NULL values in PostgreSQL

Question

I have a model with a non-nullable CharField and 2 x nullable CharField:

class MyModel(models.Model):
    name = models.CharField('Name', max_length=255, null=False)
    title = models.CharField('Title', max_length=255, blank=True)
    position = models.CharField('Position', max_length=255, blank=True)

I want to ensure that name, title, and position are unique together, and so use a UniqueConstraint:

def Meta:
    constraints = [
            models.UniqueConstraint(
                fields=['name', 'title', 'position'],
                name="unique_name_title_position"
            ),
        ]

However, if title is None then this constraint fails.

Looking into why, this is because you can insert NULL values into columns with the UNIQUE constraint because NULL is the absence of a value, so it is never equal to other NULL values and not considered a duplicate value. This means that it's possible to insert rows that appear to be duplicates if one of the values is NULL.

What's the correct way to strictly enforce this uniqueness in Django?

Make the nullable columns also non-nullable? What problem are you trying to solve with this? — yedpodtrzitko, Sep 28 '21 at 13:30
@yedpodtrzitko That would not meet my use case. E.g. if `title` and `position` is NULL I want only one `name`. If only `position` is NULL I want one `name` and `title`. — alias51, Sep 28 '21 at 13:39

iklinac · Answer 1 · 2021-09-28T13:50:07.703

UniqueConstraint are not enforced on Django level but rather directly on database.

PostgreSQL for instance allows multiple Null records for unique while MSSQL does not

You can rather check if for each subset of columns with CheckConstraint but as you will find out this is tedious as it requires each combination to be uniquely indexed.

Rather to avoid even whole this mess you can follow Django guide for CharField as documented

Avoid using null on string-based fields such as CharField and TextField. If a string-based field has null=True, that means it has two possible values for “no data”: NULL, and the empty string. In most cases, it’s redundant to have two possible values for “no data;” the Django convention is to use the empty string, not NULL. One exception is when a CharField has both unique=True and blank=True set. In this situation, null=True is required to avoid unique constraint violations when saving multiple objects with blank values.

Empty string is checked for uniqueness

score 0 · Answer 2 · answered Mar 16 '23 at 01:10

You just need to add one more constraint when title is NULL

models.UniqueConstraint(
    name='only_one_null',
    fields=('name', 'position'),
                condition=Q(title__isnull=True)
)

This should guarantee uniqueness with NULL values in title. You can add more constraints as you wish for NULL values

score -1 · Answer 3 · answered Sep 28 '21 at 14:53

You could pick values that don't ever occur for title and position and use an index like this:

CREATE UNIQUE INDEX ON mytable (
   name,
   coalesce(title, '#impossible#'),
   coalesce(position, '#impossible#'),
);

That will replace the NULL values with something else, so that duplicates are prevented.

How to enforce uniqueness with NULL values in PostgreSQL

3 Answers3