I am generally wary of cascaded deletes (and other automatic actions that could drop/damage data), either via triggers or
ON <something> CASCADE. Such facilities are very powerful, but also potentially dangerous.
- So, is cascading delete a correct choice here?
It would certainly do what you are looking for it to do: remove related records when a parent record is removed, without you needing to implement any other logic to ensure that children get removed first therefore making your code more concise. All the actions will be wrapped in an implicit transaction so if something blocks the child deletes the whole operation is blocked, maintaining referential integrity with little or no extra coding effort.
Make sure that your use of cascaded deletes and other "behind the scenes" actions are well documented so future maintainers of the system are fully aware of it.
- When cascading detele should not be used?
It should not be used if you are paranoid like me! One key point to consider is the other developers who currently, or may in future, work on your code/database (hence the comment above about documenting any "hidden" behaviours).
It is quite common in my experience for inexperienced people to use
DELETE then re-
INSERT in order to update rows, especially when what they really want is a
UPSERT operation (update existing rows and create new ones where a row with a given key does not exist) and the DBMS doesn't support merge/upsert (or they are unaware of its support). Without cascaded actions this is perfectly safe (or will error when it threatens data integrity) but if someone does this for rows in a parent table where referring FKs have
ON DELETE CASCADE set then related data will be deleted as a result of the initial delete and not replaced - so data is lost (not that even if the delete and subsequent insert are wrapped in explicit transactions, the cascade happens with the delete operation - it won't wait to see if the transaction replaces rows in the parent table in subsequent statements) and the cascade could continue through other relation ships (for instance: delete a senior supervisor, his team get deleted by cascade, his teams' teams get deleted by cascade, all the tracked records for all those people get deleted by cascade, ...). Without cascading enabled, you would just get an error here instead of the data being silently lost.