Django Postgres Intermediary doesn't set ON DELETE CASCADE

[James Bellaby]

I'm having a little trouble with my "through" table. I've set the modal to have two Foreign Key fields. Ex. below:

class Person(models.Model):
    title = models.CharField(max_length=255)


class Group(models.Model):
    title = models.CharField(max_length=255)
    members= models.ManyToManyField(Person, through='Membership', through_fields=('group', 'person'))


class Membership(models.Model):
    group = models.ForeignKey(Group, on_delete=models.CASCADE)
    person = models.ForeignKey(Person, on_delete=models.PROTECT)

I want it to keep the "Person" entries when deleted but if a group is deleted in needs to cascade.

However when looking are the SQL in Postgresql it's created the Membership table constraint for the Group id with "ON DELETE NO ACTION"

CONSTRAINT groups_membership_group_id_d4404a8c_fk_groups_group_id FOREIGN KEY (group_id)
        REFERENCES public.groups_group (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED,

My understanding was that setting CASCADE on the group in the Membership modal would set ON DELETE CASCADE for the Membership table CREATE?

If it's not a bug and I've missed something, I apologies in advance.

Cheers,

James

[Melvyn Sopacua]

On zaterdag 23 juni 2018 00:56:36 CEST James Bellaby wrote:

 

> However when looking are the SQL in Postgresql it's created the Membership

> table constraint for the Group id with "ON DELETE NO ACTION"

>

> CONSTRAINT groups_membership_group_id_d4404a8c_fk_groups_group_id FOREIGN

> KEY (group_id)

> REFERENCES public.groups_group (id) MATCH SIMPLE

> ON UPDATE NO ACTION

> ON DELETE NO ACTION

> DEFERRABLE INITIALLY DEFERRED,

>

> My understanding was that setting CASCADE on the group in the Membership

> modal would set ON DELETE CASCADE for the Membership table CREATE?

>

> If it's not a bug and I've missed something, I apologies in advance.

 

You missed the part where Django implements this at the application level by emulating this. This is (among other things) to be able to run signals, which it can't do if the database is doing the deletion.

 

--

Melvyn Sopacua

[James Bellaby]

OK, understood. However, If you set up CASCADE on the model surely when it creates the table on the database level it surely should set ON DELETE CASCADE not ON DELETE NO ACTION on the CONSTRAINT?

[Jason]

Not quite.  If you run python manage.py sqlmigrate <appname> <migration_name>, you can see the SQL generated for that migration.

https://docs.djangoproject.com/en/2.0/ref/django-admin/#django-admin-sqlmigrate

Because Django emulates Cascade, its done outside of the db, and therefore shouldn't be a db-level constraint.

[Melvyn Sopacua]

The case for and against can be made pretty much with the same argument:
- for: if a row is deleted outside of Django, so by direct database
manipulation, then the relations become inconsistent, so having database
reflect models prevents this.
- against: if a row is deleted outside of Django, so by direct database
manipulation, then signals are not processed and objects are deleted
regardless. The consequences of this are unpredictable and application
specific.

Django chose to not align model relations with database representation.
Knowing this means you have to handle things through Django exclusively where
it matters.

--
Melvyn Sopacua

[James Bellaby]

OK. So it's by design.

So during development I can't go straight to the database and delete a "Group" quickly due to an error I made. I'd have to set up tests to deal with it at an application level.

No probs though. I'm just happy I know it can't be done and not that it's a bug I'd have to wait for.

Thanks for the answers :)

[Jason]

well, nothing stopping you from doing the same in the django shell and doing `Group.objects.get(pk = some_pk).delete`.  that would be an alternative for going straight to the db.

I can see some issues with this coming up, especially if you're doing deletes with django's raw sql capability.  But that should never happen, you should do a select then iterate and delete.

[Tomasz Knapik]

If you search about it on the Internet many sources claim that Django does not set those constraints on the database level.

If you look at the code of the base code for database backends, you'll notice that they don't mention on_delete at all.

- https://github.com/django/django/blob/6dd4edb1b4f5441c5f543e29395039839c50d10b/django/db/backends/base/schema.py#L983-L1000

- https://github.com/django/django/blob/6dd4edb1b4f5441c5f543e29395039839c50d10b/django/db/backends/base/schema.py#L72-L75

And postgres does not seem to override that behaviour - https://github.com/django/django/blob/6dd4edb1b4f5441c5f543e29395039839c50d10b/django/db/backends/postgresql/schema.py

I may be looking in a wrong place, but I think it's just Django not using database-leve constraints. As long as you use Django's ORM they are enforced, I guess.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/d489db86-aeea-4272-9fc9-a1d5ffcd3736%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[James Bellaby]

Indeed! I have actually used the admin site to do it before I posted this it's just something I didn't know was by design. After numerous searches I came here but I may have been asking the wrong questions in google :). 

The shell would be good if I have quite a few I need to delete. Thankfully I had only one entry.

Thanks for help, Jason.

[Simon Charette]

Hey James,

Just to add to what was already said there's a ticket tracking the addition of

database level foreign constraints.[0]

Cheers,

Simon

[0] https://code.djangoproject.com/ticket/21961