You can see that many questions have being asked about multiple cascade paths. For example:
However, from what I see and understand, it's totally OK that you want to delete a child record on many and not just one condition of the deletion of the related master records.
While in a question it's said that SQL Server tries to be safe by preventing this from happening, I really don't get it that what might go wrong if we have multiple cascade paths, and what problems it prevents to make it safe?
I hope someone can explain that to me, in plain and simple words, preferably using examples of what might go wrong in case of multiple cascade paths.