UniqueTogetherValidator doesn't enforce uniqueness of NULL values on databases that DO enforce them

[GertBurger]

Checklist

• Raised initially as discussion #... (No. Thought an issue would help other people in the future)
• This cannot be dealt with as a third party library. (We prefer new functionality to be in the form of third party libraries where possible.)
• I have reduced the issue to the simplest possible case.

With #2452 code was introduced that changed the behavior of UniqueTogetherValidator to ignore NULL values.

The reasoning being that Postgresql (and supposedly the SQL standard) considers each instance of NULL unique from every other instance (last paragraph of https://www.postgresql.org/docs/13/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS). Many RDBMs follow this but Oracle DB doesn't.

Oracle DB will enforce multi column unique constraints properly even when some of the values are NULL. But DRF will skip the uniqueness check and a constraint violation will be returned by Oracle. Traceback at the end of the ticket.

I'm not too sure how such variation between DB backends should be treated. Skipping the None check in UniqueTogetherValidator seems to work as expected. I guess a setting that controls how NULL values are treated could work but it seems a bit out of place.

Thoughts?

Traceback with Oracle DB:

File "/.../lib/python3.9/site-packages/rest_framework/mixins.py", line 19, in create
  self.perform_create(serializer)
File "...api.py", line 585, in perform_create
  serializer.save()
File "/.../lib/python3.9/site-packages/rest_framework/serializers.py", line 205, in save
  self.instance = self.create(validated_data)
File "/.../lib/python3.9/site-packages/rest_framework/serializers.py", line 939, in create
  instance = ModelClass._default_manager.create(**validated_data)
File "/.../lib/python3.9/site-packages/django/db/models/manager.py", line 85, in manager_method
  return getattr(self.get_queryset(), name)(*args, **kwargs)
File "/.../lib/python3.9/site-packages/django/db/models/query.py", line 453, in create
  obj.save(force_insert=True, using=self.db)
File "/.../lib/python3.9/site-packages/django/db/models/base.py", line 739, in save
  self.save_base(using=using, force_insert=force_insert,
File "/.../lib/python3.9/site-packages/django/db/models/base.py", line 776, in save_base
  updated = self._save_table(
File "/.../lib/python3.9/site-packages/django/db/models/base.py", line 881, in _save_table
  results = self._do_insert(cls._base_manager, using, fields, returning_fields, raw)
File "/.../lib/python3.9/site-packages/django/db/models/base.py", line 919, in _do_insert
  return manager._insert(
File "/.../lib/python3.9/site-packages/django/db/models/manager.py", line 85, in manager_method
  return getattr(self.get_queryset(), name)(*args, **kwargs)
File "/.../lib/python3.9/site-packages/django/db/models/query.py", line 1270, in _insert
  return query.get_compiler(using=using).execute_sql(returning_fields)
File "/.../lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 1416, in execute_sql
  cursor.execute(sql, params)
File "/.../lib/python3.9/site-packages/django/db/backends/utils.py", line 98, in execute
  return super().execute(sql, params)
File "/.../lib/python3.9/site-packages/django/db/backends/utils.py", line 66, in execute
  return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
File "/.../lib/python3.9/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
  return executor(sql, params, many, context)
File "/.../lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
  return self.cursor.execute(sql, params)
File "/.../lib/python3.9/site-packages/django/db/utils.py", line 90, in __exit__
  raise dj_exc_value.with_traceback(traceback) from exc_value
File "/.../lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
  return self.cursor.execute(sql, params)
File "/.../lib/python3.9/site-packages/django/db/backends/oracle/base.py", line 523, in execute
  return self.cursor.execute(query, self._param_generator(params))
django.db.utils.IntegrityError: ORA-00001: unique constraint (SOME.TABLE__F9A18610_U) violated

[stale]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[auvipy]

can you please share update with the issue? I am not oracle expert but can this be handled in database model or django level then DRF? also can you share some failing test case for better understanding?

[nijel]

Django 5.0 supports this in ORM (PostgreSQL 15+ only): https://docs.djangoproject.com/en/5.0/ref/models/constraints/#nulls-distinct

[chatne]

@auvipy The problem for us is skipping the unique check when any of the values is None regardless of nulls_distinct-value in the unique constraint used.

None not in checked_values
https://github.com/encode/django-rest-framework/blob/dbac145638758413b966c3418fa5f3f651e3e02a/rest_framework/validators.py#L176C31-L176C57

When nulls_distinct is set to false, nulls in the data are not considered distinct values meaning only one null-value is allowed and duplicates with nulls are possible.
Skipping the check here causes the operation to fail later in the insert due to integrity error.