Cannot use cascade deletion with a self-referencing models

[tartieret]

There are some features which are not supported yet. Please check the Limitations first to see if your bug is listed.

Software versions

• Django: 3.2
• mssql-django: 1.1.3
• python: 3.9
• SQL Server: mcr.microsoft.com/mssql/server:2017-latest (docker image)
• OS: Ubuntu (docker)

Table schema and Model

Consider the example below with Client and Folder models. Folders are linked to each other in a tree structure by referencing each other.

from django.db import models

class Client(models.Model):
    name = models.CharField(max_length=10)

class Folder(models.Model):
    client = models.ForeignKey(Client, on_delete=models.CASCADE)

    parent = models.ForeignKey(
        "self",
        on_delete=models.RESTRICT,
        null=True,
        blank=True,
    )

Database Connection Settings N/A

Problem description and steps to reproduce

client1 = Client.objects.create(name="Client1")
folder1 = Folder.objects.create(client=client1)
folder2 = Folder.objects.create(client=client1, parent=folder1)
client1.delete()

Expected behavior and actual behavior

As folder instances are related through a "restrict" foreign key, we should not be able to delete a folder without first deleting all of its "children". However, thanks for the specific behaviour of the "restrict" policy for foreign key (https://docs.djangoproject.com/en/4.1/ref/models/fields/#django.db.models.RESTRICT), deleting the client object should delete all the related folders through a cascade operation.

Instead, calling client1.delete() raises an exception and fails.

Error message/stack trace

[2022-11-16 00:54:56] CRIT: Uncaught exception (in thread 140521493911360).
Traceback (most recent call last):
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.9/site-packages/mssql/base.py", line 598, in execute
    return self.cursor.execute(sql, params)
pyodbc.IntegrityError: ('23000', '[23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The DELETE statement conflicted with the REFERENCE constraint "ems_folder_client_id_bdc64bb3_fk_ems_client_id". The conflict occurred in database "test_db", table "dbo.ems_folder", column \'client_id\'. (547) (SQLExecDirectW)')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/usr/local/lib/python3.9/code.py", line 90, in runcode
    exec(code, self.locals)
  File "<console>", line 1, in <module>
  File "/usr/local/lib/python3.9/site-packages/django/db/models/base.py", line 967, in delete
    return collector.delete()
  File "/usr/local/lib/python3.9/site-packages/django/db/models/deletion.py", line 429, in delete
    count = query.delete_batch(pk_list, self.using)
  File "/usr/local/lib/python3.9/site-packages/django/db/models/sql/subqueries.py", line 43, in delete_batch
    num_deleted += self.do_query(self.get_meta().db_table, self.where, using=using)
  File "/usr/local/lib/python3.9/site-packages/django/db/models/sql/subqueries.py", line 23, in do_query
    cursor = self.get_compiler(using).execute_sql(CURSOR)
  File "/usr/local/lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 1175, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 98, in execute
    return super().execute(sql, params)
  File "/usr/local/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 "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.9/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python3.9/site-packages/mssql/base.py", line 598, in execute
    return self.cursor.execute(sql, params)
django.db.utils.IntegrityError: ('23000', '[23000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The DELETE statement conflicted with the REFERENCE constraint "ems_folder_client_id_bdc64bb3_fk_ems_client_id". The conflict occurred in database "test_db", table "dbo.ems_folder", column \'client_id\'. (547) (SQLExecDirectW)')

Any other details that can be helpful

Note that this case works perfectly with PostgreSQL and psycopg2.

[mShan0]

Hi @tartieret, thanks for the detailed report. We are able to reproduce this and are looking into it.

[tartieret]

@mShan0 any update on this? thanks for your help

[mShan0]

Hi @tartieret, apologies for the delay. This is in our backlog and will be worked on. However, no concrete timeline for when it will be fixed.