Friday, January 20, 2023

MySQL table is missing ON DELETE CASCADE constraint

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
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 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 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,

No comments:

Post a Comment