With soft deletes, a table keeps a row in place while normal application views treat it as removed. Instead of running DELETE FROM orders WHERE id = 25, the database updates a marker like is_deleted = true or deleted_at = CURRENT_TIMESTAMP. Queries for active records filter out rows with that marker, admin screens can still review them, and restore queries can bring the row back without rebuilding it from backups or audit logs. Soft deletes fit business tables where removed data still has value for support, reporting, recovery, legal review, or user-facing undo flows.
Row State in a Soft Delete Table
The row state is the part of the table that tells us how a row should be treated after a delete request. With a hard delete, the row is removed from the table after the DELETE statement finishes. With a soft delete, the row stays in place and receives a marker that says it should no longer appear in normal active-data views. That marker can be a flag, a timestamp, or a small group of audit fields that store more context about the delete event.
Flag Based Deletes
Boolean-style markers give the table a compact active-or-removed state. We add a column such as is_deleted, then store one value for active rows and another value for removed rows. New rows should start as active, so the column usually gets a NOT NULL rule and a default value.
CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY,
email VARCHAR(255) NOT NULL,
full_name VARCHAR(255) NOT NULL,
is_deleted BOOLEAN NOT NULL DEFAULT FALSE
);The default value lets an insert focus on the business fields instead of repeating the delete marker every time. When a new customer is added, the row starts with is_deleted = FALSE unless the insert says something else.
INSERT INTO customers (customer_id, email, full_name)
VALUES (25, 'alex@example.com', 'Alex Obregon');To mark that row as removed, we update the flag instead of deleting the record from the table:
UPDATE customers
SET is_deleted = TRUE
WHERE customer_id = 25;After that update, the same customer_id, email, and name remain stored. Only the row state changed. An admin screen can still inspect the record, and a report can still include it when the report is meant to include removed records.
SQL data types vary by database engine. PostgreSQL supports BOOLEAN, while SQL Server commonly uses BIT, where 0 can mean active and 1 can mean removed.
CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY,
email VARCHAR(255) NOT NULL,
full_name VARCHAR(255) NOT NULL,
is_deleted BIT NOT NULL DEFAULT 0
);The flag style is easy to read because the column answers a narrow question. The row is marked as deleted, or it is not. That can be enough for smaller tables, internal tools, or data that only needs a yes-or-no delete state. The limit is that a flag has no time, actor, or reason attached to it. We can tell the row was marked, but we can’t tell when the change happened or who made it without storing more fields.
Timestamp Based Deletes
Date and time markers carry more information than a flag. With this style, active rows keep deleted_at as NULL, and removed rows store the time of removal. The column becomes both the marker and the timestamp for the delete event.
CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY,
email VARCHAR(255) NOT NULL,
full_name VARCHAR(255) NOT NULL,
deleted_at TIMESTAMP NULL
);The NULL value means no delete event has been recorded for that row. When we mark the row as removed, we store the current timestamp.
UPDATE customers
SET deleted_at = CURRENT_TIMESTAMP
WHERE customer_id = 25
AND deleted_at IS NULL;The extra condition on deleted_at keeps the first removal time from being overwritten by a repeated delete request. If the row already has a timestamp, the update does not touch it. That can matter for support review because the first delete time is usually the meaningful event.
Some databases have their own date and time types and functions. In SQL Server, deleted_at should use a date and time type such as DATETIME2, with SYSUTCDATETIME() when the table stores UTC values.
UPDATE customers
SET deleted_at = SYSUTCDATETIME()
WHERE customer_id = 25
AND deleted_at IS NULL;UTC works well for application tables because people, services, and jobs may run across different time zones. The stored value remains consistent, and the display layer can convert it for the person reading it later.
The timestamp style usually gives a better record than a flag because it stores the removed state and the removal time in the same column. It can also support later retention rules because the table has a date that can be compared with a cutoff. The row can be active, recently removed, or removed long ago, all based on deleted_at. Some schemas store both is_deleted and deleted_at, but those values need to agree every time the row changes. If is_deleted = TRUE while deleted_at IS NULL, the row has conflicting state. For most tables, deleted_at alone is enough because NULL already means active and a timestamp already means removed.
Audit Columns
Extra delete metadata gives the row more business context. We can record who marked the row as removed, why it was removed, or which internal process handled the change. Support staff, admin review, compliance checks, and internal reporting can all benefit from that extra context.
ALTER TABLE customers
ADD deleted_by BIGINT NULL;
ALTER TABLE customers
ADD delete_reason VARCHAR(500) NULL;These fields are normally nullable because active rows do not have a delete user or delete reason. The values belong to the removal event, not to the original customer record.
When we mark the row as removed, we can set the audit fields during the same update:
UPDATE customers
SET deleted_at = CURRENT_TIMESTAMP,
deleted_by = 7,
delete_reason = 'Customer requested account closure'
WHERE customer_id = 25
AND deleted_at IS NULL;The deleted_by value can point to an internal user, an admin account, or a service identity, depending on how the system tracks who made the change. The delete_reason value can store free text, but a controlled reason code is usually better for reports because the same reason gets stored the same way every time.
ALTER TABLE customers
ADD delete_reason_code VARCHAR(50) NULL;Code like USER_REQUEST, DUPLICATE_RECORD, or ADMIN_REMOVAL is easier to group in reports than several versions of the same phrase typed by different people. Longer notes can still live in a separate field or audit table when the business needs more detail.
Delete metadata should be treated as a group. If a removed row becomes active again, the delete timestamp, delete user, and delete reason should be cleared at the same time. Leaving old removal metadata on an active record can confuse support screens and reports because the active row would still carry stale delete details.
Foreign Rows
Related tables need careful handling because a soft-deleted row still exists. A foreign key checks that the referenced row is present, and a marked parent row is still present. That means the database can keep the relationship valid while the application keeps the parent out of normal active-data views.
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
order_total DECIMAL(10, 2) NOT NULL,
CONSTRAINT orders_customer_fk
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
);If a customer row receives a deleted_at value, an order can still point to that same customer_id. That is useful for history because invoices, shipments, refunds, and support cases can keep their original customer reference.
The tradeoff is that the foreign key does not know the business meaning of the soft delete marker. It can confirm that the customer row exists, but it does not automatically reject every new child row that points to a removed customer. If new orders should only be created for active customers, we need that rule in the write flow that creates the order.
Child rows may need their own delete state too:
CREATE TABLE customer_notes (
note_id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
note_text VARCHAR(1000) NOT NULL,
deleted_at TIMESTAMP NULL,
CONSTRAINT customer_notes_customer_fk
FOREIGN KEY (customer_id)
REFERENCES customers (customer_id)
);That structure lets a note be removed without removing the customer. It also lets a customer be marked as removed without physically deleting the notes. Parent and child rows can also have separate lifecycles, which helps when the relationship needs historical data.
Soft deletes also change how cascade behavior should be handled. Database-level ON DELETE CASCADE runs when a physical delete happens. Marking a parent row with deleted_at does not physically delete child rows, so a cascade delete does not run. If child rows should be marked as removed when the parent is marked, that state change needs to be part of the delete flow through application logic, a stored procedure, or a database trigger.
Queries Built Around Soft Deletes
Daily reads and updates are where soft deletes start to affect how queries are written. The row marker helps only when reads, indexes, uniqueness rules, and recovery statements all treat the marker in the same way. We still keep removed rows in the table, but normal application queries need active data by default, while admin and recovery queries need access to the full table when that wider view is needed.
Active Filters
Most application reads should treat deleted_at IS NULL as part of the normal active-row condition. Without that condition, a list page, search result, API response, or background job can pull rows that the application already treats as removed.
SELECT customer_id, email, full_name
FROM customers
WHERE deleted_at IS NULL
ORDER BY customer_id;That query leaves the data unchanged. It only decides which rows are visible to the read. The table may still contain removed customers, but this read returns the rows that have no recorded delete timestamp.
Search queries need the same active-row condition. If we search by email and leave out the soft delete filter, a removed row can appear where the application expects only active customers.
SELECT customer_id, email, full_name
FROM customers
WHERE deleted_at IS NULL
AND email = 'alex@example.com';When the same active-row filter appears across several reads, a view can keep normal customer reads focused on active records. We can put the filter in the view definition, then let application reads query that view.
CREATE VIEW active_customers AS
SELECT customer_id, email, full_name
FROM customers
WHERE deleted_at IS NULL;The view gives application queries a smaller read surface, but the base table still contains removed rows. Admin screens, recovery tools, audit exports, and retention jobs may still need direct access to customers. The difference comes from intent. Normal application reads use the active view or active filter, while operational queries can read the wider table when removed records belong in the result.
For list pages with extra search fields, the active-row filter should stay near the business filters that decide what the user sees:
SELECT customer_id, email, full_name
FROM customers
WHERE deleted_at IS NULL
AND full_name LIKE 'Kai%'
ORDER BY full_name;We can read that query from top to bottom as active customers first, then a name search within that active set.
Active Row Indexes
Indexes become more important when a table keeps removed rows for a long time. Most reads may care only about active rows, while the table still stores both active and removed data. The index should match the way the table is read during common lookup flows.
PostgreSQL can create a partial index for active rows. The index stores entries only for rows that match the predicate:
CREATE INDEX customers_active_email_idx
ON customers (email)
WHERE deleted_at IS NULL;Queries that search active customers by email can read from that smaller active-row index:
SELECT customer_id, email, full_name
FROM customers
WHERE deleted_at IS NULL
AND email = 'kaitlyn@example.com';SQL Server has filtered indexes for the same general need. The index contains the rows that match the filter predicate, so an active-only search can avoid placing removed rows in that access route.
CREATE INDEX customers_active_name_idx
ON customers (full_name)
INCLUDE (deleted_at)
WHERE deleted_at IS NULL;That index supports name-based active customer lookups without treating removed rows as part of the same active search set.
MySQL does not have the same partial-index syntax with a WHERE clause on CREATE INDEX. A common MySQL option is a composite index that starts with the delete marker and then stores the lookup column.
CREATE INDEX customers_deleted_email_idx
ON customers (deleted_at, email);That index fits queries that filter by deleted_at first and then search by email.
SELECT customer_id, email, full_name
FROM customers
WHERE deleted_at IS NULL
AND email = 'pippin@example.com';The main idea is not that every database uses identical syntax. We want the index to match the active-row read path. A table with soft deletes can become much larger than the active data set, so indexing every row in the same way can waste space and add extra write cost. PostgreSQL and SQL Server can express active-only indexes directly. MySQL usually reaches the same goal through composite indexes or generated-column choices based on the schema and database version.
Unique Values
Values that must stay unique need extra care because soft-deleted rows remain in the table. If email must be unique, a removed row with email = 'alex@example.com' can still block a new active row with that same email when the table has a normal unique constraint.
ALTER TABLE customers
ADD CONSTRAINT customers_email_unique UNIQUE (email);That rule treats active and removed rows the same. It is valid when the business wants an email to stay reserved forever, including after the row is marked as removed. Some systems want that behavior because old records must keep ownership of the identifier.
Other systems want uniqueness only among active rows. PostgreSQL can do that with a unique partial index:
CREATE UNIQUE INDEX customers_active_email_unique
ON customers (email)
WHERE deleted_at IS NULL;SQL Server can use a unique filtered index for the same active-row rule:
CREATE UNIQUE INDEX customers_active_email_unique
ON customers (email)
WHERE deleted_at IS NULL;With that rule, two active customers cannot share the same email, but a removed customer does not block the email from being reused by a new active customer. This matches the behavior many account, profile, and catalog tables need after soft deletion.
MySQL needs a different expression because it does not have the same filtered unique index syntax. One option is a generated column that stores the email only for active rows, then a unique index on that generated value.
CREATE TABLE customers (
customer_id BIGINT PRIMARY KEY,
email VARCHAR(255) NOT NULL,
full_name VARCHAR(255) NOT NULL,
deleted_at TIMESTAMP NULL,
active_email VARCHAR(255)
GENERATED ALWAYS AS (
CASE
WHEN deleted_at IS NULL THEN email
ELSE NULL
END
) STORED,
UNIQUE KEY customers_active_email_unique (active_email)
);Because MySQL unique indexes allow multiple NULL values, removed rows can produce NULL for active_email without competing with active rows. Active rows produce their email value, so the table still blocks duplicate active emails. This rule should match the product decision. If removed rows should reserve their old value, a normal unique rule fits. If only active rows should compete, an active-only uniqueness rule fits better, with syntax chosen for the database engine.
Restore Queries
Restore queries reverse the soft delete marker and move a row back into the active set. With the timestamp style, restoration sets deleted_at back to NULL. If the table also stores delete metadata, those fields should be cleared during the same update.
UPDATE customers
SET deleted_at = NULL,
deleted_by = NULL,
delete_reason = NULL
WHERE customer_id = 25
AND deleted_at IS NOT NULL;The condition keeps the statement focused on rows that are currently removed. It also avoids rewriting active rows that do not need restoration. After the update completes, normal active filters can see the row again because deleted_at IS NULL is true.
Restores should check uniqueness rules before the update runs. If a removed customer has an email that now belongs to a different active customer, restoring the old row can violate the active-only unique index or create a business conflict.
SELECT active_customer.customer_id
FROM customers active_customer
JOIN customers removed_customer
ON active_customer.email = removed_customer.email
WHERE removed_customer.customer_id = 25
AND removed_customer.deleted_at IS NOT NULL
AND active_customer.deleted_at IS NULL;If that query returns a row, the restore needs a decision before changing state. The application may ask for a new email, reject the restore, or send the case to an admin flow that can merge records.
PostgreSQL can return the restored row from the update with RETURNING:
UPDATE customers
SET deleted_at = NULL,
deleted_by = NULL,
delete_reason = NULL
WHERE customer_id = 25
AND deleted_at IS NOT NULL
RETURNING customer_id, email, full_name;The returned row can help an admin tool confirm which record was restored. For databases without RETURNING on UPDATE, the application can run a follow-up SELECT after the update.
Restore logic should treat the active-row marker and delete metadata as one state change. We are not creating a new customer row, changing the primary key, or rebuilding relationships. We are changing the existing row back to active, so the surrounding checks need to protect the business rules that active rows must follow.
Conclusion
Soft deletes change deletion from physical removal into a controlled row state. We mark the record with is_deleted or deleted_at, make active reads filter around that marker, and add indexes and uniqueness rules that match how active rows are searched. Restore queries then clear the marker and any delete metadata after checking for conflicts, so the same row can return to the active set without losing its original identity.
