Understanding Django's UniqueConstraint nulls_distinct for Flexible Uniqueness

Purpose

• The nulls_distinct attribute (introduced in Django 5.0) controls how NULL values are treated within this constraint.
• In Django models, UniqueConstraint ensures that a combination of fields in a database table has unique values.

Behavior

• When nulls_distinct is False:

  • Any combination of fields, including those with NULL values, must be unique.
  • This imposes a stricter uniqueness requirement.

• When nulls_distinct is True (the default on most database backends):

  • Multiple rows with the same combination of non-NULL field values and any number of NULL values in the constrained fields will be considered distinct (not unique).
  • This allows for flexibility in handling NULLs while still enforcing uniqueness for non-NULL combinations.

Example

from django.db import models

class Product(models.Model):
    name = models.CharField(max_length=100)
    category = models.CharField(max_length=50)
    sku = models.CharField(max_length=20, unique=True)  # Unique by default

    class Meta:
        constraints = [
            UniqueConstraint(fields=['name', 'category'], nulls_distinct=True),
        ]

In this example:

• The UniqueConstraint on name and category allows rows with different combinations of non-NULL values for those fields, even if they have NULL values in the other field. However, rows with identical non-NULL values in both fields must be unique.
• sku has a unique constraint by default.

• If you're using an older version of Django (before 5.0), you'll need to define separate unique constraints to handle NULL values differently.
• nulls_distinct is currently not supported by all database backends. Check your specific database documentation for compatibility.

---

Strict Uniqueness (nulls_distinct=False)

This example enforces strict uniqueness for all combinations of fields, including those with NULL values.

from django.db import models

class Order(models.Model):
    customer_name = models.CharField(max_length=100)
    order_number = models.CharField(max_length=20)
    item_code = models.CharField(max_length=10)

    class Meta:
        constraints = [
            UniqueConstraint(fields=['customer_name', 'order_number', 'item_code'], nulls_distinct=False),
        ]

In this scenario, no two rows can have the same combination of customer_name, order_number, and item_code, regardless of whether any of the fields are NULL.

Handling Multiple Nullable Fields (nulls_distinct=True)

This example allows rows with different combinations of NULL values in the two nullable fields while maintaining uniqueness for non-NULL combinations.

from django.db import models

class User(models.Model):
    username = models.CharField(max_length=50, unique=True)  # Standard unique constraint
    email = models.EmailField(unique=True)  # Another unique constraint

    first_name = models.CharField(max_length=30, null=True, blank=True)
    last_name = models.CharField(max_length=30, null=True, blank=True)

    class Meta:
        constraints = [
            UniqueConstraint(fields=['first_name', 'last_name'], nulls_distinct=True),
        ]

Here, you can have multiple users with NULL values in first_name or last_name, but no two users can have the same non-NULL combination for both fields.

Customizing Constraint Name (Using name)

This example demonstrates assigning a custom name to the UniqueConstraint for better readability:

from django.db import models

class Book(models.Model):
    title = models.CharField(max_length=200)
    author = models.CharField(max_length=100)

    class Meta:
        constraints = [
            UniqueConstraint(fields=['title', 'author'], nulls_distinct=True, name='unique_book'),
        ]

---

Separate Unique Constraints

• This approach provides explicit control but can lead to more verbose model definitions.
• Define multiple UniqueConstraint instances, one for rows with non-NULL values and another for handling NULL combinations (if needed).

from django.db import models

class Product(models.Model):
    name = models.CharField(max_length=100)
    category = models.CharField(max_length=50)

    class Meta:
        constraints = [
            UniqueConstraint(fields=['name', 'category'], condition=Q(name__isnull=False, category__isnull=False)),  # Non-NULL constraint
            # Optional: Additional constraint for rows with NULLs (if needed)
            # UniqueConstraint(fields=['name', 'category'], condition=Q(name__isnull=True | category__isnull=True)),
        ]

Custom Database Validation (for Strict Uniqueness)

• This approach offers more flexibility but requires additional code and might have performance implications for large datasets.
• Implement a custom validation function using models.signals.pre_save.connect to check for duplicate records across all combinations of fields, including NULLs.

from django.db.models.signals import pre_save
from django.dispatch import receiver

@receiver(pre_save, sender=Product)
def validate_product_uniqueness(sender, instance, **kwargs):
    existing_products = Product.objects.filter(
        Q(name=instance.name, category=instance.category) |
        Q(name__isnull=True, category=instance.category) |
        Q(name=instance.name, category__isnull=True)
    )
    if existing_products.exists():
        raise ValidationError("Product with this name and category (or NULLs) already exists.")

pre_save.connect(validate_product_uniqueness)

• For complex scenarios, evaluate the trade-offs between code complexity, performance, and maintainability when choosing a suitable alternative.
• For older versions of Django or unsupported database backends, consider separate unique constraints or custom validation based on your specific requirements.
• If you need fine-grained control over NULL handling and are using Django 5.0 with a compatible database, db.models.UniqueConstraint.nulls_distinct is the recommended approach.