47

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

Community
  • 1
  • 1
Ian G
  • 29,468
  • 21
  • 78
  • 92

2 Answers2

97

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?

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • 1
    Just curious, if customer doesn't exist what good will ORDER entry do? – codersofthedark Oct 01 '16 at 20:27
  • 3
    Customer **will** exist, because the delete of customer will fail if we aren't allowing cascade to order. – Tony Andrews Oct 02 '16 at 11:40
  • 9
    @codersofthedark One real-world scenario is that if an admin happens to delete a user by mistake, it shouldn't delete the orders, or the whole accounting department will go for a toss. – ankush981 Dec 14 '16 at 11:22
13

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.

Galwegian
  • 41,475
  • 16
  • 112
  • 158