CODEWITHSUNDEEP
sql-serversql-server-2008delete-row
zsharp
zsharp

Reputation: 13756

Deleting Database Rows and their References-Best Practices

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?

Upvotes: 2

Views: 1577

Answers (3)

Eric Z Beard
Eric Z Beard

Reputation: 38426

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.

Upvotes: 3

Tom H
Tom H

Reputation: 47392

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.

Upvotes: 5

just somebody
just somebody

Reputation: 19247

if you have the foreign keys set with ON DELETE CASCADE, it'll take care of pruning your database with just DELETE master WHERE id = :x

Upvotes: 1

Related Questions