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&changeset=on&milestone=on&ticket=on&wiki=on>) 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 -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To post to this group, send email to django-developers@googlegroups.com. Visit this group at https://groups.google.com/group/django-developers. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/fbcd7bd1-4376-4006-8fa1-3dfea310fb37%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
