btholt / btholt/complete-intro-to-sql

Bug in the explanation, found in the Many-to-Many Relationships section, of why cascade deletes are undesirable

Open
#21 0 comments 0 reactions 0 assignees View on GitHub
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.