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

[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
>  hangeset=on=on=on=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 "" 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

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 "" 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.