[FIXED] Django unique_together with nullable ForeignKey

Issue

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.

Solution

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.

Answered By - Ivan