A Comprehensive Guide to Soft Delete for Rows and Columns in WordPress Custom Tables

Soft delete is a technique that allows you to logically delete data without physically removing it from the database. It is particularly useful in scenarios where data recovery or audit trails are essential. In this blog, we will explore how to implement soft delete for rows and columns in WordPress custom tables, using real SQL queries.

Why Use Soft Delete?

Soft delete provides the following advantages:

• Data Recovery: Deleted data can be restored easily since it isn’t permanently removed.
• Audit Trails: Track when and why a record or column was marked as deleted.
• Improved Flexibility: Avoid unintended data loss, especially in collaborative or dynamic systems.

Soft Delete for Rows

To implement soft delete for rows in a WordPress custom table, we add a deleted_at column. This column tracks when a row was marked as deleted. Rows with NULL in the deleted_at column are considered active, while those with a timestamp are considered deleted.

Table Schema Example

CREATE TABLE `wp_custom_table` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT NOT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` DATETIME DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Marking a Row as Deleted

UPDATE `wp_custom_table`
SET `deleted_at` = NOW()
WHERE `id` = 1;

Restoring a Deleted Row

UPDATE `wp_custom_table`
SET `deleted_at` = NULL
WHERE `id` = 1;

Querying Active Rows

SELECT * FROM `wp_custom_table`
WHERE `deleted_at` IS NULL;

Querying Deleted Rows

SELECT * FROM `wp_custom_table`
WHERE `deleted_at` IS NOT NULL;

Soft Delete for Columns

Soft deleting a column means marking it as inactive without physically removing it from the database. This approach is less common than row-based soft delete but can be useful for maintaining backward compatibility or temporarily disabling a column.

Table Schema Example

CREATE TABLE `wp_custom_table` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT NOT NULL,
    `extra_column` VARCHAR(255) NULL COMMENT 'Soft-deleted column',
    `column_status` ENUM('active', 'soft_deleted') DEFAULT 'active',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Marking a Column as Deleted

Marking a column as soft deleted involves updating its metadata or associated status field.

ALTER TABLE `wp_custom_table` MODIFY COLUMN `extra_column` VARCHAR(255) NULL COMMENT 'Soft-deleted';
UPDATE `wp_custom_table` SET `column_status` = 'soft_deleted';

Reactivating a Column

To restore a soft-deleted column, update its status back to active.

ALTER TABLE `wp_custom_table` MODIFY COLUMN `extra_column` VARCHAR(255) NULL COMMENT '';
UPDATE `wp_custom_table` SET `column_status` = 'active';

Removing a Soft-Deleted Column Permanently

If a column is no longer needed, it can be dropped safely after confirming it is marked as soft deleted.

ALTER TABLE `wp_custom_table` DROP COLUMN `extra_column`;

Best Practices for High-Traffic WordPress Sites

1. Use Transactions: Wrap your operations in transactions to ensure data integrity.
2. Schedule Changes: Perform schema changes during low-traffic periods.
3. Optimize Queries: Add indexes to frequently queried columns, such as deleted_at or column_status.
4. Backup Your Data: Always take a backup before making schema changes.
5. Test in Staging: Validate your queries in a staging environment before applying them to production.

Conclusion

Soft delete is a powerful technique for managing rows and columns in WordPress custom tables. It provides flexibility, enhances data safety, and allows for better auditing. Whether you are managing rows with a deleted_at column or handling columns with metadata, soft delete can improve the robustness of your WordPress applications.