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

Answer 2

UPDATE: previous version of my answer was functional but had bad design, this one takes in account some of the comments and other answers.

In SQL NULL does not equal NULL. This means if you have two objects where field_d == None and field_c == "somestring" they are not equal, so you can create both.

You can override Model.clean to add your check:

class ModelB(Model):
    #...
    def validate_unique(self, exclude=None):
        if ModelB.objects.exclude(id=self.id).filter(field_c=self.field_c, \
                                 field_d__isnull=True).exists():
            raise ValidationError("Duplicate ModelB")
        super(ModelB, self).validate_unique(exclude)

If used outside of forms you have to call full_clean or validate_unique.

Take care to handle the race condition though.

Answer 3

@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)
    ]

Answer 4

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.")