Should I use the CASCADE DELETE rule?

Question

I've always been too scared to use DELETE CASCADE, but as I get more confident (lazy :D), I'm thinking how bad can it be, is it best practise to use it or should I avoid it and clean up my foreign keys etc the old fashioned way (with stored procedures)?

Answer 1

I prefer having control over exactly what is deleted (by explicitly declaring it), so I generally opt to delete via stored procedures, and not to use cascading deletes.

Answer 2

See this question:
http://stackoverflow.com/questions/59297/whenwhy-to-use-cascading-in-sql-server

Answer 3

this was good

http://www.mytechmusings.com/2008/04/using-foreign-keys-in-mysql.html

Answer 4

ON DELETE CASCADE is fine, but only when the dependent rows are really a logical extension of the row being deleted. For example, its OK for DELETE ORDERS to delete the associated ORDER_LINES because clearly you want to delete this order, which consists of a header and some lines. On the other hand, DELETE CUSTOMER should not delete the associated ORDERS because ORDERS are important in their own right, they are not just attributes of a customer.

One way to think about this is: if I issue DELETE X and it also deletes Y, will I be happy or unhappy? And if I issue DELETE X and am told "cannot delete X because Y exists" will I be glad of the protection, or irritated at the incovenience?