CODEWITHSUNDEEP
mysqldatabasedatabase-designforeign-keys
Martin Heralecký
Martin Heralecký

Reputation: 5789

MySQL indirect ON DELETE CASCADE for 1:1 relationship

I have a super-type entity and its sub-type, there is one to one relationship, and the ON DELETE is set to CASCADE (so when the super-type is deleted, its sub-type is deleted also). The sub-type also has relationship with some other, non-related entity - this relationship also has ON DELETE CASCADE.

super_type
  id

sub_type
  id
  super_type_id   ON DELETE CASCADE  # references super_type.id
  other_table_id  ON DELETE CASCADE  # references other_table.id

When some record from other_table gets deleted, so does the corresponding sub_type record. But this leaves a super_type record that has no sub_type attached. How can I prevent this?

Note: I can't make "double-linked relationship" (adding a foreign key to super_type referencing to sub_type), because there are more than one sub-type.

Upvotes: 1

Views: 117

Answers (1)

luksch
luksch

Reputation: 11712

Your problem can be solved with triggers. Unfortunately not easily, since mysql has the very annoying "feature" of not running triggers if a row gets deleted by cascading referential integrity checks, i.e. in your case.

What I did in the past to avoid this problem was to remove the cascading deletions in the table description. Instead have triggers on deletion of any row in super_type, sub_type and other_table that delete:

super-type - trigger BEFORE DELETE to delete the sub-type entries

sub-type - trigger AFTER DELETE to delete the super-type

other-table - trigger AFTER DELETE to delete any sub-type that references it.

Here a link to the msql-behavior feature/bug: https://bugs.mysql.com/bug.php?id=61555

Upvotes: 2

Related Questions