Django unique_together with nullable ForeignKey

Question

I'm using Django 1.8.4 in my dev machine using Sqlite and I have these models:

class ModelA(Model):
    field_a = CharField(verbose_name='a', max_length=20)
    field_b = CharField(verbose_name='b', max_length=20)

    class Meta:
        unique_together = ('field_a', 'field_b',)


class ModelB(Model):
    field_c = CharField(verbose_name='c', max_length=20)
    field_d = ForeignKey(ModelA, verbose_name='d', null=True, blank=True)

    class Meta:
        unique_together = ('field_c', 'field_d',)

I've run proper migration and registered them in the Django Admin. So, using the Admin I've done this tests:

• I'm able to create ModelA records and Django prohibits me from creating duplicate records - as expected!
• I'm not able to create identical ModelB records when field_b is not empty
• But, I'm able to create identical ModelB records, when using field_d as empty

My question is: How do I apply unique_together for nullable ForeignKey?

The most recent answer I found for this problem has 5 year... I do think Django have evolved and the issue may not be the same.

Answer 1

Django 2.2 added a new constraints API which makes addressing this case much easier within the database.

You will need two constraints:

1. The existing tuple constraint; and
2. The remaining keys minus the nullable key, with a condition

If you have multiple nullable fields, I guess you will need to handle the permutations.

Here's an example with a thruple of fields that must be all unique, where only one NULL is permitted:

from django.db import models
from django.db.models import Q
from django.db.models.constraints import UniqueConstraint

class Badger(models.Model):
    required = models.ForeignKey(Required, ...)
    optional = models.ForeignKey(Optional, null=True, ...)
    key = models.CharField(db_index=True, ...)

    class Meta:
        constraints = [
            UniqueConstraint(fields=['required', 'optional', 'key'],
                             name='unique_with_optional'),
            UniqueConstraint(fields=['required', 'key'],
                             condition=Q(optional=None),
                             name='unique_without_optional'),
        ]