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

**URL:** <https://forum.djangoproject.com/t/how-to-add-a-unique-constraint-between-a-foreign-key-and-a-nullable-date-field-only-if-the-date-field-is-null/35553>\
**Category:** Using the ORM\
**Created:** [October 10, 2024, 3:48am UTC](https://forum.djangoproject.com/t/how-to-add-a-unique-constraint-between-a-foreign-key-and-a-nullable-date-field-only-if-the-date-field-is-null/35553 "2024-10-10T03:48:18Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![suspiciousRaccoon](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/suspiciousraccoon/32/16844_2.png) [@suspiciousRaccoon](https://forum.djangoproject.com/u/suspiciousRaccoon)\
**Post date:** [October 10, 2024, 3:48am UTC](https://forum.djangoproject.com/t/how-to-add-a-unique-constraint-between-a-foreign-key-and-a-nullable-date-field-only-if-the-date-field-is-null/35553/1 "2024-10-10T03:48:18Z")

</div>

I have 2 models:

```python
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:

```bash
>>> 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

```

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [October 10, 2024, 3:56am UTC](https://forum.djangoproject.com/t/how-to-add-a-unique-constraint-between-a-foreign-key-and-a-nullable-date-field-only-if-the-date-field-is-null/35553/2 "2024-10-10T03:56:08Z")

</div>

Check out the [nulls\_distinct](https://docs.djangoproject.com/en/5.1/ref/models/constraints/#django.db.models.UniqueConstraint.nulls_distinct) attribute for your constraint. I think that may work for you here.

---

<div class="post-metadata">

**Author:** ![suspiciousRaccoon](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/suspiciousraccoon/32/16844_2.png) [@suspiciousRaccoon](https://forum.djangoproject.com/u/suspiciousRaccoon)\
**Post date:** [October 10, 2024, 4:28am UTC](https://forum.djangoproject.com/t/how-to-add-a-unique-constraint-between-a-foreign-key-and-a-nullable-date-field-only-if-the-date-field-is-null/35553/3 "2024-10-10T04:28:24Z")

</div>

Thanks, for the quick answer!

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

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

```