Sql – Should I use the CASCADE DELETE rule?


Duplicate of: When/Why to use Cascading in SQL Server?

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)?

Best Solution

ON DELETE CASCADE is fine, but only when the dependent rows are really a logical extension of the row being deleted. For example, it's 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 inconvenience?