# Soft deletes affect database constraints, indexes, and application queries

DevFeed: [Soft deletes affect database constraints, indexes, and application queries](<https://devfeed.tech/articles/soft-deletes-are-a-schema-decision-that-breaks-every-query-you-write-afterwards-39592.md>)

Original publisher: [Read original article](<https://ankit-rana.com/logs/40-soft-deletes-schema-decision/>)

Author: hello@ankit-rana.com

Published: 2026-08-20T00:00:00Z

Content type: opinion

Language: en

Sources: [Ankit Rana | Mechanical Sympathy](<https://devfeed.tech/sources/ankit-rana-mechanical-sympathy.md>)

Topics: [data-processing](<https://devfeed.tech/topics/data-processing.md>), [Database](<https://devfeed.tech/topics/database.md>), [MySQL](<https://devfeed.tech/topics/mysql.md>)

Tags: [data-modelling](<https://devfeed.tech/tags/data-modelling.md>), [database-design](<https://devfeed.tech/tags/database-design.md>), [foreign-keys](<https://devfeed.tech/tags/foreign-keys.md>), [indexes](<https://devfeed.tech/tags/indexes.md>), [indexing](<https://devfeed.tech/tags/indexing.md>), [mysql](<https://devfeed.tech/tags/mysql.md>), [orm](<https://devfeed.tech/tags/orm.md>), [postgresql](<https://devfeed.tech/tags/postgresql.md>), [predicate](<https://devfeed.tech/tags/predicate.md>), [schema](<https://devfeed.tech/tags/schema.md>), [schema-design](<https://devfeed.tech/tags/schema-design.md>), [soft-delete](<https://devfeed.tech/tags/soft-delete.md>)

## AI overview

The article explains how soft deletion affects database design beyond adding a deleted_at column. It discusses duplicate-key failures, foreign-key behavior, queries that may return deleted rows, and index inefficiency, noting that PostgreSQL partial indexes help while MySQL requires a workaround.

## Source excerpt

A deleted_at column turns every future query into a conditional one, and the cost is not the extra predicate. Unique constraints stop working because the deleted row still occupies the key, foreign keys start pointing at rows the application considers gone, and any query written by someone who does not know about the column silently returns deleted data. Soft delete is a data lifecycle decision, and treating it as a boolean column is what makes it expensive.