ManyToManyField not generating ON DELETE CASCADE clauses on (default) through table

[Carlton]

I have a query concerning the lack of a ON DELETE CASCADE clause to the foreign key constraints generated for PostgreSQL (using Django 1.8.11) on the default through tables generated for ManyToManyField.

I was unable to find any related issues (”ManyToMany cascade” Search Results – Django) so I thought I’d ask before I open a ticket.

I create an app called constraints in which I define two models:

from django.db import models
class A(models.Model):
 name = models.CharField(max_length=200)

class B(models.Model):
 name = models.CharField(max_length=200)
 a_set = models.ManyToManyField(A, db_constraint=True)

I then inspect the generated SQL:

$ ./manage.py sqlall constraints

BEGIN;

CREATE TABLE "constraints_a" (

    "id" serial NOT NULL PRIMARY KEY,

    "name" varchar(200) NOT NULL

)

;

CREATE TABLE "constraints_b_a_set" (

    "id" serial NOT NULL PRIMARY KEY,

    "b_id" integer NOT NULL,

    "a_id" integer NOT NULL REFERENCES "constraints_a" ("id") DEFERRABLE INITIALLY DEFERRED,

    UNIQUE ("b_id", "a_id")

)

;

CREATE TABLE "constraints_b" (

    "id" serial NOT NULL PRIMARY KEY,

    "name" varchar(200) NOT NULL

)

;

ALTER TABLE "constraints_b_a_set" ADD CONSTRAINT "b_id_refs_id_4308660a" FOREIGN KEY ("b_id") REFERENCES "constraints_b" ("id") DEFERRABLE INITIALLY DEFERRED;

CREATE INDEX "constraints_b_a_set_b_id" ON "constraints_b_a_set" ("b_id");

CREATE INDEX "constraints_b_a_set_a_id" ON "constraints_b_a_set" ("a_id");

COMMIT;

For the table constraints_b_a_set I am expecting the two foreign key constraints to include an ON DELETE CASCADE:

• "a_id" integer NOT NULL REFERENCES "constraints_a" ("id") ON DELETE CASCADE ...
• ALTER TABLE "constraints_b_a_set" ADD CONSTRAINT "b_id_refs_id_4308660a" FOREIGN KEY ("b_id") REFERENCES "constraints_b" ("id") ON DELETE CASCADE ...

I came across this running DELETE FROM ... SQL statements against a Django generated database.

Using the example models if I run DELETE FROM "constraints_a";` (with related objects in place) I get an error:

ERROR: update or delete on table "constraints_a" violates foreign key constraint "<long_indentifier>" on table "constraints_b_a_set" 

DETAIL: Key (id)=(1) is still referenced from table "constraints_b_a_set".

This is because the lack of the ON DELETE CASCADE means the through table record is not removed when the referenced row is deleted.

For me this looks like a bug. First question then is, is it a bug? Or is it by design, and something that just needs working around? Does it need documenting?

Second (related) question is, would anything break if I just added the constraint in SQL myself?

I can work around this by an additional query to DELETE FROM the through table, and — whilst there’s a lot of logic there that made my head hurt — it looks as if Django does this in django/django/db/models/deletion.py — but it would be nice to let the database handle the constraint if possible.

Would Django’s own logic break if we pushed it to the database level — or, conversely, is there room to do that? 

I’m happy to spend time on it.

(No doubt, having raised it, the answer will be, “No, not bug; not open to change, and documented HERE” 😀)

Thanks for the input/guidance. 

Kind Regards, 

Carlton

[Shai Berger]

Hi Carlton,

I'm sorry that your mail was left unanswered for so long.

Generally, "is it a bug" questions are better suited to django-users than to
this list. In particular, I believe this is not a bug: The behavior you see is
Django's default way of defining foreign keys, and indeed, as you noticed, when
you delete the objects using Django's API, it takes care to delete the
dependent records as well.

The reason for Django's preference to delete the dependent records itself is,
that this way we can fire signals for the to-be-deleted objects. The reason for
not including ON DELETE CASCADE in the column definition is that this way, if
there's a problem and for some reason an object that should be deleted is not
deleted, it is found out early.

I don't think anything will break terribly if you add ON DELETE CASCADE to the
FK constraints, but I have to wonder why you'd prefer to delete the records
through SQL directly rather than through Python, where you have all the
handlers ready.

But as noted, this discussion doesn't really belong on this list.

HTH,
Shai.

On Tuesday 15 March 2016 21:43:11 Carlton wrote:
> I have a query concerning the lack of a ON DELETE CASCADE clause to the
> foreign key constraints generated for PostgreSQL (using Django 1.8.11) on
> the default through tables generated for ManyToManyField.
>
>
> I was unable to find any related issues (”ManyToMany cascade” Search
> Results – Django

> <https://code.djangoproject.com/search?q=manytomany+cascade&noquickjump=1&c
> hangeset=on&milestone=on&ticket=on&wiki=on>) so I thought I’d ask before I

> - "a_id" integer NOT NULL REFERENCES "constraints_a" ("id") ON DELETE
> CASCADE ...
> - ALTER TABLE "constraints_b_a_set" ADD CONSTRAINT

> "b_id_refs_id_4308660a" FOREIGN KEY ("b_id") REFERENCES "constraints_b"
> ("id") ON DELETE CASCADE ...
>
> I came across this running DELETE FROM ... SQL statements against a Django
> generated database.
>
>
> Using the example models if I run DELETE FROM "constraints_a";` (with
> related objects in place) I get an error:
>
>
> ERROR: update or delete on table "constraints_a" violates foreign key
> constraint "<long_indentifier>" on table "constraints_b_a_set"
>
> DETAIL: Key (id)=(1) is still referenced from table "constraints_b_a_set".
>
>
> This is because the lack of the ON DELETE CASCADE means the through table
> record is not removed when the referenced row is deleted.
>
>

> For me this looks like a bug. *First question then is, is it a bug?* Or is

> it by design, and something that just needs working around? Does it need
> documenting?
>
>
> Second (related) question is, would anything break if I just added the
> constraint in SQL myself?
>
>
> I can work around this by an additional query to DELETE FROM the through
> table, and — whilst there’s a lot of logic there that made my head hurt
> — it looks as if Django does this in django/django/db/models/deletion.py —
> but it would be nice to let the database handle the constraint if possible.
>
>
> Would Django’s own logic break if we pushed it to the database level — or,
> conversely, is there room to do that?
>
>
> I’m happy to spend time on it.
>
>

> (No doubt, having raised it, the answer will be, *“No, not bug; not open to
> change, and documented HERE”* 😀)