Menu
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)?
707
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 delete cascade is a particularly bad thing to use to clean data because it doesn't discriminate against the data you want the FK to stop the delete for and the data you are trying to completely purge.
A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in SQL Server.
ON DELETE CASCADE clause in MySQL is used to automatically remove the matching records from the child table when we delete the rows from the parent table. It is a kind of referential action related to the foreign key.
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?
196
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
