Symfony World blog is not maintained anymore. Check new sys.exit() programming blog.

Many-to-many relations with on delete cascade

Scene from "Ghostbusters" by Ivan Reitman (1984)

The following article is based on a section from the symfony doctrine book, where an example schema of many-to-many relation is defined. The part that is interesting is defining relations of BlogPostTag:

relations:
    BlogPost:
      foreignAlias: BlogPostTags
    Tag:
      foreignAlias: BlogPostTags

In many situations, it is very useful to add the ON DELETE CASCADE option there, since the BlogPostTag (or any other many-to-many relation table) will have constraints disabling deletion of related records. In other words, if you leave the schema the way it is now, you may find (using an admin generator for example) you can't delete a BlogPost or a Tag if there's at least one BlogPostTag joining the record you want to delete. Example database error message will look like:

Cannot delete or update a parent row: a foreign key constraint fails
(`table`, CONSTRAINT `index` FOREIGN KEY (`column`) REFERENCES `table` (`id`))]

This is because of the constraint which is created in the database - it wants you to delete joining records (BlogPostTag) first, then there are no constraints on both Tag and BlogPost tables. To solve the problem (make the database automatically delete related BlogPostTag records whenever a Tag or BlogPost is deleted), simply add two lines to the text:

relations:
    BlogPost:
      foreignAlias: BlogPostTags
      onDelete: CASCADE
    Tag:
      foreignAlias: BlogPostTags
      onDelete: CASCADE
Rerun building your model and recreate your database. From now on, there shall be no constraint fails on your database.

5 comments:

  1. Thanks, nice article! I've posted it to the Symfony Developers group on Diigo

    ReplyDelete
  2. Very helpful post. It would be even nicer if you add few words of intro describing the problem a bit for those who are lazy to follow the link and read the Symfony book. Something like "Suppose we'd like to delete the Blog record .."
    Thanks!

    ReplyDelete