The Database Cannot Decide What Deletion Means

A constraint can block a delete, but it cannot define the policy

Account deletion often begins life as a single repository call. Then the data model grows: profile media, addresses, audit records, appointments, subscriptions, and records with legal or operational retention requirements all point toward the same principal.

At that point, a failed DELETE is not merely a database problem. It is evidence that the application has not stated what deletion means across its boundaries.

A recent merged .NET change exposed this clearly. Removing an account failed when certain dependent rows existed. The database correctly protected referential integrity, but the application also surfaced raw provider details to the UI. The correction did not respond by enabling cascade delete everywhere. It classified the dependencies and gave each class a deliberate outcome.

The evidence discussed here is limited to committed code and focused tests. No database or test suite was rerun for this article.

Start with ownership, not foreign keys

For every dependent record, ask who owns the deletion decision. Three categories are usually enough:

1. Owned data exists only as part of this account and can be removed with it.
2. Retained data has a lifecycle that can outlive the account, so deletion must be refused or transformed according to an explicit policy.
3. Externally owned data belongs to another module or service and requires coordination through that boundary.

This classification is more useful than asking whether a foreign key can cascade. A cascade expresses a storage action. It does not explain retention, ownership, auditability, or cross-module responsibility.

Stage owned removals under one commit boundary

Owned dependants should normally be staged with the principal and committed together. Here is original illustrative code, not code from a private system:

public async Task<DeleteResult> DeleteAccountAsync(
    AccountId accountId,
    CancellationToken cancellationToken)
{
    var plan = await deletionPolicy.ClassifyAsync(accountId, cancellationToken);

    if (plan.RetainedRecords.Count > 0)
        return DeleteResult.Refused("Some records require a separate retention decision.");

    if (plan.ExternalDependencies.Count > 0)
        return DeleteResult.RequiresCoordination();

    db.RemoveRange(plan.OwnedDependants);
    db.Remove(plan.Account);

    await db.SaveChangesAsync(cancellationToken);
    return DeleteResult.Completed();
}

The important detail is what is absent: there is no early save after removing owned dependants.

Saving them first creates an asymmetric failure. If the principal deletion later fails, the account remains but some of its profile data is already gone. Staging everything on one context lets the ORM order dependent and principal statements and lets the transaction succeed or fail as a unit.

If an identity store or framework component owns SaveChanges, verify that it shares the same context and transaction boundary. That assumption is load-bearing and deserves a focused test or a documented contract.

Refusal is a valid domain outcome

Some records should not disappear merely because the account owner asked to leave. Their retention may be controlled by law, finance, safety, dispute resolution, or another explicit policy.

That does not mean “never delete.” It means the account-deletion command is not authorised to decide. A refusal should name the next required decision in safe, human language and should happen before destructive work starts.

This is a useful leadership habit: distinguish a technical inability from a deliberate refusal. One is an accident. The other is a product and governance decision that can be reviewed.

Keep module boundaries honest

A module should not reach into another module's tables simply because a foreign key blocks its command. Doing so quietly transfers ownership and creates coupling that will surface again during migrations, testing, or service extraction.

Externally owned dependencies need an explicit coordination mechanism: a higher-level workflow, a domain event with a durable consumer, or a refusal that tells an operator what must be resolved first. The right mechanism depends on consistency requirements. The key is that the boundary remains visible.

Translate infrastructure failure without hiding the evidence

Raw database messages can reveal schema names, table names, constraint names, and implementation details. They are useful in protected logs, not in an end-user response.

Map expected conflicts to stable domain outcomes. Log the technical exception with the normal correlation context, then return a safe explanation. Avoid catching every exception as “cannot delete”; cancellation, connectivity failures, and programming defects still need their normal owners.

Test the policy, not only the happy path

The most valuable tests pin the classification:

• Owned dependants are staged, but not committed early.
• Retained records cause refusal before any delete is attempted.
• External dependencies are not silently removed across a module boundary.
• Provider errors become safe messages rather than raw database text.
• Logically hidden rows do not create a blind spot in the policy.

Unit tests can verify orchestration. A relational integration test is stronger evidence for actual foreign-key behaviour and statement ordering. Label each test by the claim it proves.

A practical deletion review

Before changing a cascade rule, draw the dependency map and mark every edge owned, retained, or external. Confirm the commit boundary. Define refusal and coordination outcomes. Decide what the user sees and what operators can diagnose. Then test each branch.

The trade-off is explicit code and an ownership map that must evolve with the schema. The return is worth it: deletion becomes predictable policy rather than whatever the database happens to permit.

What would your current account-deletion path do with each of those three dependency classes?