Migration failure making unique column non-nullable: Could not drop constraint

[sparrowt]

Problem

Modifying a field which is nullable and unique, to no longer be nullable, results in the following error during migration:

...
  File "...\venv.37\lib\site-packages\django\db\backends\base\schema.py", line 535, in alter_field
    old_db_params, new_db_params, strict)
  File "...\venv.37\lib\site-packages\sql_server\pyodbc\schema.py", line 270, in _alter_field
    self._delete_unique_constraints(model, old_field, new_field, strict)
  File "...\venv.37\lib\site-packages\sql_server\pyodbc\schema.py", line 479, in _delete_unique_constraints
    self.execute(self._delete_constraint_sql(self.sql_delete_unique, model, constraint_name))
  File "...\venv.37\lib\site-packages\sql_server\pyodbc\schema.py", line 681, in execute
    cursor.execute(sql, params)
  File "...\venv.37\lib\site-packages\django\db\backends\utils.py", line 99, in execute
    return super().execute(sql, params)
  File "...\venv.37\lib\site-packages\django\db\backends\utils.py", line 67, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "...\venv.37\lib\site-packages\django\db\backends\utils.py", line 76, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "...\venv.37\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "...\venv.37\lib\site-packages\django\db\utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "...\venv.37\lib\site-packages\django\db\backends\utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "...\venv.37\lib\site-packages\sql_server\pyodbc\base.py", line 559, in execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]'myapp_mymodel_my_field_a717c0d4_uniq' is not a constraint. (3728) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Could not drop constraint. See previous errors. (3727)")

Reproduction steps

This is reproducible in a minimal Django project with these dependencies:

Django==2.2.10
django-mssql-backend==2.4.2
pyodbc==4.0.30
pytz==2019.3
sqlparse==0.3.0

and the following single model:

class MyModel(models.Model):
    my_field = models.CharField(max_length=100, null=True, unique=True)

1. makemigrations to get 0001 which creates this model with 1 field that is null=True, unique=True
2. migrate which does the following (note: the uniqueness is enforced by a filtered unique index rather than by a constraint on the table, since this change: Nullable unique constraints #1)

CREATE TABLE [myapp_mymodel] ([id] int IDENTITY (1, 1) NOT NULL PRIMARY KEY, [my_field] nvarchar(100) NULL)
CREATE UNIQUE INDEX [myapp_mymodel_my_field_a717c0d4_uniq] ON [myapp_mymodel]([my_field]) WHERE [my_field] IS NOT NULL

3. then modify my_field in models.py by removing null=True
4. makemigrations to get 0002 which does an AlterField
5. migrate which tries to do the following in preparation (before altering the nullability)

ALTER TABLE [myapp_mymodel] DROP CONSTRAINT [myapp_mymodel_my_field_a717c0d4_uniq]

but blows up with the error & stack trace above, because myapp_mymodel_my_field_a717c0d4_uniq is an index, not a constraint.

Cause

I believe this is a knock-on effect of #1 and this is as far as I got with diagnosing the problem:

• _alter_field calls _delete_unique_constraints because of the 'Nullability change', which calls _constraint_names(..., unique=True)
• _constraint_names returns ['myapp_mymodel_my_field_a717c0d4_uniq'] despite this being an index, and then _delete_unique_constraints tries to delete it using sql_delete_unique (i.e. ALTER TABLE ... DROP CONSTRAINT ...) which fails because it is just an index

Possible solution

Perhaps _delete_unique_constraints should call _constraint_names twice as follows:

• once with unique=True, index=False and DROP those using ALTER TABLE... DROP CONSTRAINT
• once with unique=True, index=True and DROP those using DROP INDEX ...

Does that sound reasonable?

[OskarPersson]

Thank you for the detailed report, I was easily able to reproduce the problem on my machine. Calling _constraint_names twice or simply adding the index=False to the existing call does not solve the problem.

I don't really have the time at this very moment to investigate this problem myself but I would gladly discuss other possible solution if you find something or open a PR.

Again, nice work with finding and reporting the bug 👍

[sparrowt]

Thank you @OskarPersson for replying so quickly!

Could you please explain a bit further why the proposed solution doesn't solve the problem? I have pushed the patch to #39 and with that patch I can no longer reproduce the issue above.

[OskarPersson]

I must have misunderstood something when I read this before, I've commented on your PR now :)

[CCattermoul]

Please see my comments here #45 - the same problem appears to be happening when removing a field that was unique and nullable. I am running from master.