Deleting Database Rows and their References-Best Practices

Question

How do I go about deleting a row that is referenced by many other tables, either as a primary key or as a foreign key?

Do I need to delete each reference in the appropriate order, or is there an 'auto' way to perform this in, for example, linq to sql?

Answer 1

If you're performing all of your data access through stored procedures then your delete stored procedure for the master should take care of this. You need to maintain it when you add a new related table, but IMO that requires you to think about what you're doing, which is a good thing.

Personally, I stay away from cascading deletes. It's too easy to accidentally delete a slew of records when the user should have been warned about existing children instead.

Answer 2

Many times the best way to delete something in a database is to just "virtually" delete it by setting an IsDeleted column, and then ignoring the row in all other queries.

Deletes can be very expensive for heavily linked tables, and the locks can cause other queries to fail while the delete is happening.

You can just leave the "IsDeleted" rows in the system forever (which might be helpful for auditing), or go back and delete them for real when the system is idle.