Should I use the CASCADE DELETE rule?

sql sql-server cascade
inspite picture inspite · Nov 10, 2008 · Viewed 55.1k times · Source

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

Answer

Tony Andrews picture Tony Andrews · Nov 10, 2008

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?

Related questions

SQL Server: drop table cascade equivalent?

In oracle, to drop all tables and constraints you would type something like DROP TABLE myTable CASCADE CONSTRAINTS PURGE; and this would completely delete the tables and their dependencies. What's the SQL server equivalent??

Read More
tsql script to add delete cascade to existing tables

is there a script that can be used to enable cascaded deletion for existing tables. Thanks.

Read More
SQL Server - Cascading DELETE with Recursive Foreign Keys

I've spent a good amount of time trying to figure out how to implement a CASCADE ON DELETE for recursive primary keys on SQL Server for some time now. I've read about triggers, creating temporary tables, etc but have yet …

Read More