btholt / btholt/complete-intro-to-sql
Bug in the explanation, found in the Many-to-Many Relationships section, of why cascade deletes are undesirable
- Dominant language
- JavaScript
- Stars
- 451
- Forks
- 67
- PR merge metrics
- No merged PRs in 30d
Description
Hello,
When creating the `recipe_ingredients` table in the Many-to-Many Relationships section we currently use `ON DELETE NO ACTION` in the column that reference the `recipes`' table primary key, and in the column that references the `ingredients`' table primary key. Also, in that same section it is stated that
> We don't want to cascade deletes because that could delete recipes and ingredients unintentionally
I don't think that's correct since, as is explained in section [5.4.5 Foreign Keys](https://www.postgresql.org/docs/14/ddl-constraints.html#DDL-CONSTRAINTS-FK) of PostgreSQL's (version 14) documentation,
> CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well
Based on that documentation we can conclude that using `ON DELETE CASCADE` on the `recipe_id` and `ingredient_id` columns of the `recipe_ingredients` table would only produce that, when either a referenced recipe or ingredient is deleted, the corresponding record in the `recipe_ingredients` table would be deleted as well. But deleting an record in the `recipe_ingredients` table would not delete records in either the `recipes` or `ingredients` tables.
Assuming the explanation in the paragraph above is true, it's more semantically appropriate to use `ON DELETE CASCADE`. For example, we wouldn't want to keep the records specifying which ingredientes are used in a recipe if we no longer have that recipe in our database (however, we can and should keep the records of the ingredients themselves since these could be used in recipes other than the one deleted).
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.