Is it a good or bad idea to use "ON UPDATE CASCADE ON DELETE CASCADE" for foreign keys? Why does this mechanism exist at all?
I'm not sure about ON UPDATE CASCADE. If you find yourself needing this sort of cascaded update then that is perhaps a &quot;code smell&quot; in your database design. In theory your primary key should be static so changes that need cascading shouldn't need to happen. Perhaps it was added as a logical step along from ON DELETE CASCADE. It is at least safer than cascading deletes.
The existence of ON DELETE CASCADE makes more sense: while PKs shouldn't really change things do often get deleted. The cascading is simply a convenience, it saves you from having to write code to drop child entities manually when getting rid of a parent. Further more it might be considered safer than implementing this in other logic because the database is taking care of transactional consistency, deadlocks, and so forth, so the operation should (bugs permitting) be guaranteed atomic. If you implement your own &quot;find children, delete, then delete parent&quot; which may have to be nested, you have to do some legwork[!] to ensure that if there is an error part way through there is no way that you delete the great-great-great-grand-children of a row but leave the rest standing (leaving a partly deleted entity which could cause difficult to diagnose issues later).
[!] Taking appropriate locks, preferably not by locking whole tables, ensuring transaction isolation settings are right, ... - it isn't as simple as it might first look.
Why Not Cascade?
As I said above, I consider a need to cascade updates routinely to be a bit of a design smell. You shouldn't need to change a primary key value during normal operations.
I'm very wary of cascaded deletes, despite the danger of bugs in more manually deleting complex structured entities. Too often you see inexperienced people perform UPSERT operations[*] using a DELETE-then-re-INSERT method, even when the DB supports single-statement upsert operations[^], which damages your data if cascaded deletes are enabled: the delete removes the children too, and they don't get put back by the subsequent insert.
Also, in a lot of cases with real data you don't actually want a cascaded delete. For example: if a manager leaves a company you don't want to delete their sub-ordinates because assigning a new manager first was forgotten, or prevented by a bug.
[*] in postgres via INSERT ... ON CONFLICT ... but this is not standard and quite different syntax[†] is used elsewhere
[^] either because they aren't aware of the available syntax, or are avoiding it in order to be cross-DB compatible
[†] MERGE can be used in Microsoft's TSQL for the same effect, mySQL[‡] supports INSERT ... ON DUPLICATE KEY ...
[‡] mySQL also supports REPLACE INTO, but IIRC that is just syntactic sugar for delete+insert so has the same dangers