[Django]-Django unique_together with nullable ForeignKey

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'),
        ]

@ivan, I don’t think that there’s a simple way for django to manage this situation. You need to think of all creation and update operations that don’t always come from a form. Also, you should think of race conditions…

And because you don’t force this logic on DB level, it’s possible that there actually will be doubled records and you should check it while querying results.

And about your solution, it can be good for form, but I don’t expect that save method can raise ValidationError.

If it’s possible then it’s better to delegate this logic to DB. In this particular case, you can use two partial indexes. There’s a similar question on StackOverflow – Create unique constraint with null columns

So you can create Django migration, that adds two partial indexes to your DB

Example:

# Assume that app name is just `example`

CREATE_TWO_PARTIAL_INDEX = """
    CREATE UNIQUE INDEX model_b_2col_uni_idx ON example_model_b (field_c, field_d)
    WHERE field_d IS NOT NULL;

    CREATE UNIQUE INDEX model_b_1col_uni_idx ON example_model_b (field_c)
    WHERE field_d IS NULL;
"""

DROP_TWO_PARTIAL_INDEX = """
    DROP INDEX model_b_2col_uni_idx;
    DROP INDEX model_b_1col_uni_idx;
"""


class Migration(migrations.Migration):

    dependencies = [
        ('example', 'PREVIOUS MIGRATION NAME'),
    ]

    operations = [
        migrations.RunSQL(CREATE_TWO_PARTIAL_INDEX, DROP_TWO_PARTIAL_INDEX)
    ]

One possible workaround not mentioned yet is to create a dummy ModelA object to serve as your NULL value. Then you can rely on the database to enforce the uniqueness constraint.

Add a clean method to your model – see below:

def clean(self):
        if Variants.objects.filter("""Your filter """).exclude(pk=self.pk).exists():
            raise ValidationError("This variation is duplicated.")

I think this is more clear way to do that for Django 1.2+

In forms it will be raised as non_field_error with no 500 error, in other cases, like DRF you have to check this case manual, because it will be 500 error.
But it will always check for unique_together!

class BaseModelExt(models.Model):
is_cleaned = False

def clean(self):
    for field_tuple in self._meta.unique_together[:]:
        unique_filter = {}
        unique_fields = []
        null_found = False
        for field_name in field_tuple:
            field_value = getattr(self, field_name)
            if getattr(self, field_name) is None:
                unique_filter['%s__isnull' % field_name] = True
                null_found = True
            else:
                unique_filter['%s' % field_name] = field_value
                unique_fields.append(field_name)
        if null_found:
            unique_queryset = self.__class__.objects.filter(**unique_filter)
            if self.pk:
                unique_queryset = unique_queryset.exclude(pk=self.pk)
            if unique_queryset.exists():
                msg = self.unique_error_message(self.__class__, tuple(unique_fields))

                raise ValidationError(msg)

    self.is_cleaned = True

def save(self, *args, **kwargs):
    if not self.is_cleaned:
        self.clean()

    super().save(*args, **kwargs)