Cascading foreign key DELETE with tables that have RESTRICT constraints

Question

I have a database that stores recipes for multiple organizations. So every table has an org_id pointing to the orgs table to allow each organization to have their own set of recipes and ingredients.

Recipes come from 3 tables: recipes holds the name, creation date, etc. recipe_ingredients is a list of the ingredients and how much of each. raw_materials is the raw ingredient name, type, color, etc.

Since a raw material can be used in many recipes, recipe_ingredients has ON DELETE RESTIRCT on the raw_material_id.

Since recipe_ingredients only apply to a single recipe, it also has ON DELETE CASCADE on the recipe_id.

This works as I expect--I can't delete a raw material that is used in any recipe, and deleting a recipe deletes all of its ingredients. Groovy.

Now what I'd like is to be able to delete an entire organization from the orgs table and have it cascade to everything associated with it, so all three tables have ON DELETE CASCADE for their org_id key, but it appears that the cascade hits the raw_materials table first and fails its RESTRICT foreign key constraint.

This makes sense, but is there a way around it? I've read a little about triggers, but I don't understand yet how I'd use them to get this to happen.

Relation diagram

Edit:

Ended up going with the following trigger on the orgs table:

CREATE TRIGGER `orgs_before_delete` BEFORE DELETE ON `orgs`
 FOR EACH ROW DELETE FROM recipes
WHERE recipes.org_id=OLD.indx

Answer 1

Using triggers would be a bit messy

You should do the cascade delete manually

Perhaps something like this

SET FOREIGN_KEY_CHECKS = 0;
START TRANSACTION;
SELECT indx INTO @indx_to_cascade FROM orgs WHERE ...;
DELETE FROM recipes WHERE org_id = @indx_to_cascade ;
DELETE FROM recipe_ingredients WHERE org_id = @indx_to_cascade ;
DELETE FROM raw_materials WHERE org_id = @indx_to_cascade ;
DELETE FROM orgs WHERE indx = @indx_to_cascade ;
ROLLBACK;
SET FOREIGN_KEY_CHECKS = 1;

If this happens quickly, then switch to COMMIT rather than ROLLBACK

SET FOREIGN_KEY_CHECKS = 0;
START TRANSACTION;
SELECT indx INTO @indx_to_cascade FROM orgs WHERE ...;
DELETE FROM recipes WHERE org_id = @indx_to_cascade ;
DELETE FROM recipe_ingredients WHERE org_id = @indx_to_cascade ;
DELETE FROM raw_materials WHERE org_id = @indx_to_cascade ;
DELETE FROM orgs WHERE indx = @indx_to_cascade ;
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;

• JoeFish: Thank you for this. It wasn't exactly the solution I needed, but it got me there so I'm accepting it. What worked was setting a BEFORE DELETE trigger on orgs to delete from the recipes table first, which cascaded through recipe_ingredients, freeing up raw_materials to be deleted. I'll edit the solution into my question.