OneToOneField doesn't allow multiple records with NULL value on Microsoft SQL Server

[Ryan Schave]

A OneToOneField with null=True should allow multiple records to be saved with a NULL value in the field. Consider the following models:

class Company(models.Model):
    name = models.CharField(max_length=30)


class UserProfile(models.Model):
    case_manager = models.BooleanField(default=False)
    company = models.OneToOneField(Company, models.SET_NULL, blank=True, null=True)

With a SQLite or PostgreSQL backend I'm able to save multiple records with a NULL value:

up1 = UserProfile.objects.create(case_manager=True)
up2 = UserProfile.objects.create(case_manager=False)

With Microsoft SQL Server, the 2nd attempt to create a record with a NULL value results in an integrity error:

django.db.utils.IntegrityError: ('23000', "[23000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of UNIQUE KEY constraint 'UQcontacts3E2672343AF86B58'. Cannot insert duplicate key in object 'dbo.contacts_userprofile'. The duplicate key value is (<NULL>). (2627) (SQLExecDirectW)")

This post - ​http://stackoverflow.com/a/767702 describes the problem and suggests creating a "unique filtered index based on a predicate that excludes NULLs".

I have confirmed that if I manually remove the constraint created by the migration in the table schema and create a unique filtered index that excludes NULLs on the company field I can create multiple records with a NULL value in the company field. The migration should be adding a filter to the index that looks something like:

WHERE ([company_id] IS NOT NULL)

I am able to duplicate the problem with Django 1.9.10 and 1.10.4.

[Tim Graham]

Is it certain that Django must be fixed rather than the MSSQL database backend that you're using? (which one is it?) In any case, adding a test to Django's test suite to confirm that all built-in backends behave properly seems like a good idea. Hopefully database backend that you're using will also run that test.

[Simon Charette]

I'm pretty sure that's a bug in the MSSQL server backend used by the reporter here.

MSSQL server diverges from the SQL standard by disallowing multiple NULL values in column with a UNIQUE index. The backend should make sure to create conditional indexes excluding NULL columns for fields marked null=True.

[Ryan Schave]

I can produce a test that passes w/ SQLite and PostgreSQL, but fails with MS SQL. I assume since MS SQL is not a built-in backend, there is little that can be done easily on the Django side and my experience w/ MS suggests they aren't going to address this. I created a custom migration that removes the constraint added by the initial Django migration and add a unique index that allows multiple NULLs.

I assume that the code in django/db/migrations works together with the appropriate backend in django/db/backends. Does Django use the base backend when a 3rd party database is used?

[Tim Graham]

Most likely -- look at the code of the backend you're using and see if it subclasses Django's base classes.

[Ryan Schave]

It makes sense that the 3rd party backend should handle this. There is already an open pull request (​https://github.com/michiya/django-pyodbc-azure/pull/43) for django-pyodbc-azure.

Would it be beneficial to submit the test that passes with the supported backends and fails with the the django-pyodbc-azure backend? This would at least give visuality into the problem when Django tests are run against that backend.

[Tim Graham]

It looks like removing supports_nullable_unique_constraints = False may already yield a regression test in tests/one_to_one/tests.py due to the usage of the feature flag there. Maybe other backends like django-mssql would want to fix the issue so we could remove that feature?