MySQL table is missing ON DELETE CASCADE constraint

[Dennis Tants]

Hey folks,

I recently came across what I think is a bug. Let me try to explain it as detailed as possible.
Imagine I have two models, one is called Events, the other is called EventAccounts. EventAccounts can't exist without an Event, so I added a ForeignKey with the on_delete=models.CASCADE constraint to one field of EventAccounts. Should I delete an event, the corresponding event accounts will also be deleted. This works just fine when using Django itself (or the admin pages). See my models.py.
models.py:

class Events(models.Model):
   ...

class EventAccounts(models.Model):
   ...
  account_event = models.ForeignKey(Events, on_delete=models.CASCADE)
   ...

Now one of my tasks is to cleanup the database every day to delete events, which ended more than two weeks ago. I was thinking of creating a cronjob to run raw SQL every day. When running this command:

DELETE FROM app_eventtool_events WHERE event_end_date < (NOW() - INTERVAL 14 DAY);

I always get informed that there is an existing child and the events do not get deleted. Which is why I had a look at the table constraints with:

SHOW CREATE TABLE app_eventtool_eventaccounts;

It seems that the ON DELETE CASCADE statement at the end is missing:

CONSTRAINT `app_eventtool_eventa_account_event_id_0ff3718a_fk_app_event` FOREIGN KEY (`account_event_id`) REFERENCES `app_eventtool_events` (`event_id`)

If I now delete the above constraint and add a selfmade one, it looks like this:

ALTER TABLE app_eventtool_eventaccounts ADD CONSTRAINT app_eventtool_events_account_event_id FOREIGN KEY (account_event_id) REFERENCES app_eventtool_events (event_id) ON DELETE CASCADE ON UPDATE NO ACTION;
CONSTRAINT `app_eventtool_events_account_event_id` FOREIGN KEY (`account_event_id`) REFERENCES `app_eventtool_events` (`event_id`) ON DELETE CASCADE ON UPDATE NO ACTION

As you can see, ON DELETE CASCADE is now present. Now I am also able to delete the events with raw SQL as mentioned above. This is reproducible when dropping and creating the database fully. Updating from version 4.0.6 to version 4.1.5 did not solve the problem.

Furthermore I started testing a bit with the on_delete statement in models.py. I changed models.CASCADE to models.RESTRICT and created a new migration. The migration file itself looks fine. But when trying to get the raw SQL of the migration via:

python3 manage.py sqlmigrate app_eventtool 0002

it basically results in an empty migration:

--
-- Alter field account_event on eventaccounts
--
-- (no-op)

Maybe this is expected behaviour, but for me it seems like a bug. Can anyone confirm/deny this?

Thanks in advance,
Dennis

[Bhuvnesh Sharma]

Hi dennis,

You can open a ticket on the trac (https://code.djangoproject.com/query) for whatever issue you are facing.It will be easier to understand there.

Regards

Bhuvnesh 

--
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 view this discussion on the web visit https://groups.google.com/d/msgid/django-users/cb989abd-cb08-8093-668a-03b756149be1%40uni-bremen.de.

[Jason]

https://docs.djangoproject.com/en/4.1/ref/models/fields/#django.db.models.CASCADE

> Django emulates the behavior of the SQL constraint ON DELETE CASCADE and also deletes the object containing the ForeignKey.

this is specified in the docs.  

https://code.djangoproject.com/ticket/21961

[Dennis Tants]

Thanks for the answers. Still not sure why it would not implement ON DELETE CASCADE in the DB itself. But whatever.

Regards,
Dennis

To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/67684c77-0d05-4514-b675-abe6e0eb2b20n%40googlegroups.com.

[Jason]

you can read through the comments the ticket for the reasons :)