How to add a unique constraint between a foreign key and a nullable date field only if the date field is null.

I have 2 models:

class Foo(models.Model):
    name = models.CharField(max_length=255)

class Bar(models.Model):
    foo = models.ForeignKey(Foo, on_delete=models.CASCADE)
    date = models.DateField(null=True, blank=True)

    class Meta:
        constraints = [
            models.UniqueConstraint( # This doesn't work
                fields=["foo", "date"],
                condition=models.Q(date=None),
                name="unique_foo_if_date_is_null",
            )
        ]
		

I want Bar objects to be able to have a foreign key to the same Foo object, but if the date field is null, I only want a single of the Bar objects to be in the database to avoid having duplicate Bar objects. I don’t want it to be possible to create more than 1 Bar object related to the same Foo object if the date is null.

Here is a demonstration of what I want:

>>> foo_object = Foo.objects.create(name="foo")
>>> foo_object
<Foo: Foo object (1)>
>>> bar_object_with_not_null_date_1 = Bar.objects.create(foo=foo_object, date=datetime.datetime.now())
>>> bar_object_with_not_null_date_1
<Bar: Bar object (1)>
>>> bar_object_with_not_null_date_2 = Bar.objects.create(foo=foo_object, date=datetime.datetime.now()) 
>>> bar_object_with_not_null_date_2 # this is ok
<Bar: Bar object (2)>
>>> bar_object_with_null_date_1 = Bar.objects.create(foo=foo_object)
>>> bar_object_with_null_date_1                                     
<Bar: Bar object (3)>
>>> bar_object_with_null_date_2 = Bar.objects.create(foo=foo_object) # this should throw an exception
>>> bar_object_with_null_date_2
<Bar: Bar object (4)>
>>> print(bar_object_with_null_date_1.date, bar_object_with_null_date_2.date)  # I don't want this to be possible
None None

Check out the nulls_distinct attribute for your constraint. I think that may work for you here.

1 Like

Thanks, for the quick answer!

I changed the constraint to look like this and it works now :slight_smile:

 models.UniqueConstraint(
                fields=["foo", "date"],
                nulls_distinct=False,
                name="unique_foo_if_date_is_null",
            )