Allowing Null Values in Django Unique Fields

Django provides a way to ensure uniqueness for model fields through the unique=True parameter. However, by default, Django’s unique constraint does not allow multiple NULL-values. So if you want to allow one or more NULL values in a unique field, you need to put in some extra work. In this post, we’ll explore the options for allowing NULL-values in Django unique fields.

The Problem with Null Values in Unique Fields

When you set unique=True on a Django model field, it essentially creates a UNIQUE constraint at the database level. For example:

class Product(models.Model):
    name = models.CharField(max_length=100, unique=True)

This would translate to the following SQL:

CREATE TABLE "products" (
    "id" serial NOT NULL PRIMARY KEY,
    "name" varchar(100) NOT NULL UNIQUE
);

The UNIQUE constraint ensures no two rows have the same value in that column. However, NULL

values are treated differently in SQL. Unlike a literal value, NULL represents the absence of a value. Because of this, two NULL-values are not considered equal in SQL.

This causes an issue when using null=True with unique=True in Django. If null=True allows a name to be NULL, the uniqueness check would fail when trying to save a second model instance with a NULL name.

Method 1: Allow One NULL Value

One option is to allow exactly one NULL value in the unique field. That way, all non-null-values would still need to be unique.

Django does not provide a built-in way to achieve this, but we can create a custom database-level constraint:

CREATE TABLE "products" (
    "id" serial NOT NULL PRIMARY KEY,
    "name" varchar(100) NULL UNIQUE
);

ALTER TABLE "products" 
   ADD CONSTRAINT constraint_name 
   CHECK (name IS NOT NULL OR (name IS NULL AND  
           (SELECT COUNT(*) FROM products WHERE name IS NULL) < 2));

This additional CHECK constraint ensures that if any rows have a NULL name, then at most only one row has a NULL value.

The downside to this approach is it requires raw SQL, so it may not work across different databases.

Method 2: Partial Unique Index

Another method is creating a partial unique index. This index would only enforce uniqueness for non-null values:

CREATE UNIQUE INDEX "products_name_key" ON "products" ("name") WHERE name IS NOT NULL;

We can achieve this in Django using the `indexes` option on the model `Meta`:

class Product(models.Model):
    name = models.CharField(max_length=100, null=True)
    
    class Meta:
        indexes = [
            models.Index(fields=['name'], name='products_name_idx', condition=Q(name__isnull=False))
        ]

This allows any number of rows to have NULL names, while still being able to query and filter efficiently with the index.

However, one caveat is the database will still allow saving duplicate non-NULL-values, but retrieval queries may behave unexpectedly. So additional application-level validation is recommended.

Method 3: Custom Clean Validation

The final method is performing uniqueness validation in Django instead of the database. We can override the model’s clean() method to check for duplicate non-null-values:

from django.core.exceptions import ValidationError

class Product(models.Model):
    name = models.CharField(max_length=100, null=True)

    def clean(self):
        if self.name is not None: 
            if Product.objects.filter(name=self.name).exists():
                raise ValidationError('Product with this Name already exists.')

    class Meta:  
        constraints = [models.CheckConstraint(check= models.Q(name__isnull=False), name='name_isnull')]

This does require an extra query on every model save. But it allows fine-grained control over the validation logic, like ignoring case sensitivity.

The constraints option adds a CHECK at the database level to prevent all rows from having a NULL name.

Summary

There are a few methods to allow NULL-values in Django unique fields:

• Use a database CHECK constraint to permit one NULL
• Create a partial unique index to enforce uniqueness only on non-nullvalues
• Validate uniqueness in Python code and rely on database NULL handling

The best approach depends on your specific app requirements. With some extra work, Django can accommodate models that need both nullability and uniqueness.