Problem with UniqueConstraint on fields, one of which allows NULL value.

[Андрей]

UniqueConstraint does not work correctly for several fields, one of which is nullable.

class MyModel(models.Model):

    id = models.BigAutoField(primary_key=True, editable=False)
    name = models.CharField(max_length=1000, verbose_name="Name")
    measure = models.ForeignKey(
        Measure,
        related_name="mymodel_measure",
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        verbose_name="Measure",
    )


    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=["name"],
                name="mymodel_unique_name_measure_null",
                condition=Q(measure__isnull=True),
                violation_error_message="Error message for null",
            ),
            models.UniqueConstraint(
                fields=["name", "measure"],
                name="mymodel_unique_name_measure_not_null",
                condition=Q(measure__isnull=False),
                violation_error_message="Error message for not null",
            ),
        ]

Database - Postgres 15.

The error is as follows: if there is a record with the field measure = not null, you can create a new record with the same name and measure = null. And if there is an entry with measure = null, then a new entry with the same name and measure = not null does not work.

From the Postgres side, Constraints are created and working correctly.

Error message:

{
  "error": "{'name': [ErrorDetail(string='The name of the mymodel with this Name already exists.', code='unique')]}"
}

[Sarah Boyce]

I couldn't replicate - could you share a script?
I am using postgres 15 and Django 5.1.2

>>> MyModel.objects.create(name="Test", measure=measure_1)
<MyModel: MyModel object (1)>
>>> MyModel.objects.create(name="Test")
<MyModel: MyModel object (2)>
>>> MyModel.objects.create(name="Test", measure=measure_2)
<MyModel: MyModel object (3)>
>>> MyModel.objects.all().delete()
(3, {'app1.MyModel': 3})
>>> my_model = MyModel(name="Test")
>>> my_model.full_clean()
>>> my_model.save()
>>> my_model = MyModel(name="Test", measure=measure_1)
>>> my_model.full_clean()
>>> my_model.save()
>>> my_model = MyModel(name="Test", measure=measure_2)
>>> my_model.full_clean()
>>> my_model.save()

[Андрей]

You are right that when Django is running through the SHELL, the error is not reproduced (I checked it too after your message).
However, there is an error when creating an object using the rest api.
The order is as follows:

1. Create any measure object (m1. This can be created using shell ).
2. Create mymodel with the parameter measure = null (name="test". This can be created using shell ).
3. Create mymodel from rest api (name="test", parameter=m1. This should be created via the viewset, in our case the rest api via postman/bruno ).

I get the error:

{
"name": [
"my model with that name already exists."
  ]
}

My scripts:

models.py

from django.db import models
from django.db.models import Q


class Measure(models.Model):

    id = models.AutoField(
        primary_key=True,
        verbose_name="Key",
    )
    code = models.CharField(
        max_length=5,
        unique=True,
        verbose_name="Code",
        error_messages={"unique": "Err msg."},
    )

    def __str__(self):
        return self.code


class MyModel(models.Model):

    id = models.BigAutoField(primary_key=True, editable=False)
    name = models.CharField(max_length=1000, verbose_name="Name")
    measure = models.ForeignKey(
        Measure,
        related_name="mymodel_measure",
        on_delete=models.SET_NULL,
        null=True,
        blank=True,
        verbose_name="Measure",
    )

    def __str__(self):
        return self.name

    class Meta:
        constraints = [
            models.UniqueConstraint(
                fields=["name"],
                name="mymodel_unique_name_measure_null",
                condition=Q(measure__isnull=True),
                violation_error_message="Error message for null",
            ),
            models.UniqueConstraint(
                fields=["name", "measure"],
                name="mymodel_unique_name_measure_not_null",
                condition=Q(measure__isnull=False),
                violation_error_message="Error message for not null",
            ),
        ]

views.py

from rest_framework import viewsets
from directories.serializers import MyModelSerializer
from .models import MyModel

class MyModelViewSet(viewsets.ModelViewSet):
    queryset = MyModel.objects.select_related("measure").all()
    serializer_class = MyModelSerializer

serializers.py

from rest_framework import serializers
from directories.models import MyModel

class MyModelSerializer(serializers.ModelSerializer):

    class Meta:
        model = MyModel
        fields = "__all__"

urls.py

from django.urls import path, include
from rest_framework.routers import DefaultRouter
from directories.views import MyModelViewSet


router = DefaultRouter()
router.register("mymodel", MyModelViewSet)


urlpatterns = [
    path("d/", include(router.urls)),
]

Next in postman or bruno create object (step 3):
post method, body:

{
  "name": "test",
  "measure": 1
}

and I get an error:

{
"name": [
"my model with that name already exists."
  ]
}

[Sarah Boyce]

Unless you can show that this is a bug in Django, you will need to report this to Django REST framework: ​https://github.com/encode/django-rest-framework/issues

[Андрей]

The error falls from serializer.is_valid (it is rest_framework.exceptions.ValidationError):

from directories.views import MyModelViewSet
viewset = MyModelViewSet()
new_object = viewset.create(data={'name': 'test', 'measure': 1})

Traceback (most recent call last):

File "<console>", line 1, in <module>
File "....views.py", line 15, in create

serializer.is_valid(raise_exception=True)

File "....venv\Lib\site-packages\rest_framework\serializers.py", line 231, in is_valid

raise ValidationError(self.errors)

rest_framework.exceptions.ValidationError: {'name': [ErrorDetail(string='my model with this Name already exists.', code='unique')]}