[Django] #35848: Problem with UniqueConstraint on fields, one of which allows NULL value.

[Django]

#35848: Problem with UniqueConstraint on fields, one of which allows NULL value.
-------------------------------------+-------------------------------------
Reporter: platonovap | Type: Bug
Status: new | Component:
| Uncategorized
Version: 5.1 | Severity: Normal
Keywords: UniqueConstraint, | Triage Stage:
nullable, null | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
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')]}"
}
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35848>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#35848: Problem with UniqueConstraint on fields, one of which allows NULL value.
-------------------------------------+-------------------------------------

Reporter: platonovap | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:

Keywords: UniqueConstraint, | Triage Stage:
nullable, null | Unreviewed

Has patch: 1 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by platonovap):

* component: Uncategorized => Database layer (models, ORM)
* has_patch: 0 => 1

--
Ticket URL: <https://code.djangoproject.com/ticket/35848#comment:1>

[Django]

#35848: Problem with UniqueConstraint on fields, one of which allows NULL value.
-------------------------------------+-------------------------------------
Reporter: platonovap | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: UniqueConstraint, | Triage Stage:
nullable, null | Unreviewed

Has patch: 0 | Needs documentation: 0

Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Clifford Gama):

* has_patch: 1 => 0

--
Ticket URL: <https://code.djangoproject.com/ticket/35848#comment:2>

[Django]

#35848: Problem with UniqueConstraint on fields, one of which allows NULL value.
-------------------------------------+-------------------------------------

Reporter: Андрей | Owner: (none)
Type: Bug | Status: closed

Component: Database layer | Version: 5.1

(models, ORM) | Resolution:
Severity: Normal | worksforme

Keywords: UniqueConstraint, | Triage Stage:
nullable, null | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce):

* resolution: => worksforme
* status: new => closed

Comment:

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()
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35848#comment:3>

[Django]

#35848: Problem with UniqueConstraint on fields, one of which allows NULL value.
-------------------------------------+-------------------------------------
Reporter: Андрей | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.1
(models, ORM) | Resolution:
Severity: Normal | worksforme
Keywords: UniqueConstraint, | Triage Stage:
nullable, null | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Андрей):

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."
]
}
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35848#comment:4>

[Django]

#35848: Problem with UniqueConstraint on fields, one of which allows NULL value.
-------------------------------------+-------------------------------------
Reporter: Андрей | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.1
(models, ORM) | Resolution:
Severity: Normal | worksforme
Keywords: UniqueConstraint, | Triage Stage:
nullable, null | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Андрей):

* Attachment "serializers.py" added.

--
Ticket URL: <https://code.djangoproject.com/ticket/35848>

[Django]

#35848: Problem with UniqueConstraint on fields, one of which allows NULL value.
-------------------------------------+-------------------------------------
Reporter: Андрей | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.1
(models, ORM) | Resolution:
Severity: Normal | worksforme
Keywords: UniqueConstraint, | Triage Stage:
nullable, null | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Андрей):

* Attachment "models.py" added.

[Django]

#35848: Problem with UniqueConstraint on fields, one of which allows NULL value.
-------------------------------------+-------------------------------------
Reporter: Андрей | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.1
(models, ORM) | Resolution:
Severity: Normal | worksforme
Keywords: UniqueConstraint, | Triage Stage:
nullable, null | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Андрей):

* Attachment "views.py" added.

[Django]

#35848: Problem with UniqueConstraint on fields, one of which allows NULL value.
-------------------------------------+-------------------------------------
Reporter: Андрей | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.1
(models, ORM) | Resolution:
Severity: Normal | worksforme
Keywords: UniqueConstraint, | Triage Stage:
nullable, null | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Андрей):

* Attachment "urls.py" added.

[Django]

#35848: Problem with UniqueConstraint on fields, one of which allows NULL value.
-------------------------------------+-------------------------------------
Reporter: Андрей | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.1
(models, ORM) | Resolution:
Severity: Normal | worksforme
Keywords: UniqueConstraint, | Triage Stage:
nullable, null | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Андрей):

* Attachment "settings.py" added.

[Django]

#35848: Problem with UniqueConstraint on fields, one of which allows NULL value.
-------------------------------------+-------------------------------------
Reporter: Андрей | Owner: (none)

Type: Bug | Status: new

Component: Database layer | Version: 5.1
(models, ORM) |

Severity: Normal | Resolution:

Keywords: UniqueConstraint, | Triage Stage:
nullable, null | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Андрей):

* resolution: worksforme =>
* status: closed => new

--
Ticket URL: <https://code.djangoproject.com/ticket/35848#comment:5>

[Django]

#35848: Problem with UniqueConstraint on fields, one of which allows NULL value.
-------------------------------------+-------------------------------------
Reporter: Андрей | Owner: (none)
Type: Bug | Status: new
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution:
Keywords: UniqueConstraint, | Triage Stage:
nullable, null | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by 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
--
Ticket URL: <https://code.djangoproject.com/ticket/35848#comment:6>

[Django]

#35848: Problem with UniqueConstraint on fields, one of which allows NULL value.
-------------------------------------+-------------------------------------
Reporter: Андрей | Owner: (none)

Type: Bug | Status: closed

Component: Database layer | Version: 5.1
(models, ORM) |

Severity: Normal | Resolution: invalid

Keywords: UniqueConstraint, | Triage Stage:
nullable, null | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Changes (by Sarah Boyce):

* resolution: => invalid

* status: new => closed

--
Ticket URL: <https://code.djangoproject.com/ticket/35848#comment:7>

[Django]

#35848: Problem with UniqueConstraint on fields, one of which allows NULL value.
-------------------------------------+-------------------------------------
Reporter: Андрей | Owner: (none)
Type: Bug | Status: closed
Component: Database layer | Version: 5.1
(models, ORM) |
Severity: Normal | Resolution: invalid
Keywords: UniqueConstraint, | Triage Stage:
nullable, null | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------

Comment (by Андрей):

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')]}
--
Ticket URL: <https://code.djangoproject.com/ticket/35848#comment:8>