CODEWITHSUNDEEP
sqlsql-server
user746461
user746461

Reputation:

Never use Cascade Delete becasue we want to keep all logic inside application code

I'm working on a project and its SQL Server database always use ON DELETE NO ACTION regardless, even for Order-OrderItem scenarios.

I recall school professors always ask us to set cascade delete.

Senior developers say it's because we want to keep all logic inside application code, meaning if we want to delete Order, our code has to first delete OrderItem.

Is it a valid argument? When should ON DELETE NO ACTION be used?

Upvotes: 0

Views: 188

Answers (2)

Caius Jard
Caius Jard

Reputation: 74605

Most of the time these days I see applications that make heavy use of soft delete and it's a different enough concept that a cascading hard delete wrecks it completely, especially if an app tries to mix the two approaches.

Only you can really answer whether your particular project will use cascade delete or not

Is it a valid argument?

Yes

When should ON DELETE NO ACTION be used?

When another mechanism is handling relationship maintenance by an alternative means and this mechanism would wreck the effectiveness of that mechanism

Upvotes: 0

LAS
LAS

Reputation: 819

Yes, it's not my preference but handling data integrity at the application or business layer is a known technique. This needs to be well tested to ensure the application doesn't create orphans. I've worked with systems where the developers used the business layer to handle foreign key constraints and cascade deletes. It's very difficult to understand the relationships and behavior if you're working in the backend. This needs to be well documented.

Upvotes: 0

Related Questions