Implementing Soft-Delete in pure MySQL

Soft-delete, setting a deleted flag instead of actually deleting, is a great way to ensure the ability to recover accidentally deleted data by users for many applications.

Some technologies, like Ruby on Rails, have soft-delete baked in, so the developer doesn’t have to do anything to use it.

Unfortunately, others don’t have that, and for some it is quite difficult to integrate. I’ve tried it with Entity Framework 6. It worked, but wasn’t nearly as clean as I hoped it would be.

So for the cases where its too much work to add soft-delete to the application, I’ve developed a MySQL script that adds it to the database, without having to change the application at all.

The Script

The script is a stored procedure that loops through all tables and creates the SQL statements needed to add soft-delete to the database.

CREATE DEFINER=`root`@`localhost` PROCEDURE `AddSoftDelete`()
BEGIN
    DECLARE q, result TEXT;
    DECLARE tableName TEXT;
    DECLARE columnList TEXT;

    DECLARE done1 INT DEFAULT FALSE;
    DECLARE tableNamesCursor CURSOR FOR 
    SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = "your_schema";
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;

    SET result = "";
    OPEN tableNamesCursor;
    tableLoop: LOOP
        FETCH tableNamesCursor INTO tableName;
        IF done1 THEN 
            LEAVE tableLoop;
        END IF;

        SET q = CONCAT("CREATE TABLE ", tableName, "_deleted SELECT * FROM ", tableName, " WHERE 1 = 0;");  #don't copy data and don't copy indexes
        SET result = CONCAT(result, q, "\n");

        SET q = CONCAT("ALTER TABLE ", tableName, "_deleted ADD Deleted DATETIME;");
        SET result = CONCAT(result, q, "\n");

        #Create Triggers to move removed Data
        SET result = CONCAT(result, "DELIMITER |", "\n");

        SET columnList = "";
        BLOCK2: BEGIN
            DECLARE columnName, columnTableName TEXT;
            DECLARE done2 INT DEFAULT FALSE;
            DECLARE columnNamesCursor CURSOR FOR 
            SELECT COLUMN_NAME, TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = "schema_name";
            DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = TRUE;

            OPEN columnNamesCursor;
            columnLoop: LOOP
                FETCH columnNamesCursor INTO columnName, columnTableName;
                IF done2 THEN 
                    LEAVE columnLoop;
                END IF;

                IF columnTableName = tableName THEN
                    IF columnList = "" THEN
                        SET columnList = CONCAT("OLD.", columnName);
                    ELSE
                        SET columnList = CONCAT(columnList, ", OLD.", columnName);
                    END IF;
                END IF;
            END LOOP columnLoop;
        END BLOCK2;

        SET q = CONCAT("CREATE TRIGGER ", tableName, "_delete AFTER DELETE ON ", tableName, " FOR EACH ROW BEGIN INSERT INTO ", tableName, "_deleted VALUES(", columnList, ", NOW()); END |"); #NOW() for the Deleted Column
        SET result = CONCAT(result, q, "\n");

        SET result = CONCAT(result, "DELIMITER ;", "\n");

        SET result = CONCAT(result, "\n");
    END LOOP tableLoop;

    SELECT result;
END

After adding that stored procedure, you’d call it with

call AddSoftDelete();

Then you’d take the output and execute it. For each table in your database, it creates a table with the added suffix “_deleted” with the same structure as the original table, adds a “Deleted” column and a trigger on the original table that inserts deleted columns into the “_deleted” table. The created tables won’t have any indices on them. This is to ensure that no errors occurr due to duplicate entries. Since the rows in the original table are really deleted, it is possible to have multiple rows with the same id, just not at the same time. But all these rows will end up in the “_deleted” table some day, so it should not check for duplicate data.

Here’s an example output for my “people” table (there will be one block of these statements for each table you have):

CREATE TABLE people_deleted SELECT * FROM people WHERE 1 = 0;
ALTER TABLE people_deleted ADD Deleted DATETIME;
DELIMITER |
CREATE TRIGGER people_delete AFTER DELETE ON people FOR EACH ROW BEGIN INSERT INTO people_deleted VALUES(OLD.Id, OLD.Firstname, OLD.Lastname, OLD.Sex, OLD.SocialSecurityNumber, OLD.DateOfBirth, NOW()); END |
DELIMITER ;

So that’s all there is to it. Hope it helps you!

“But what if I wanted to get rid of it again” you ask? “Surely you wrote a script for that too”. The answer is yes, indeed I did. And I will of course provide it for you.

Here it is:

CREATE PROCEDURE `RemoveSoftDelete`()
BEGIN
    DECLARE q, result TEXT;
    DECLARE tableName TEXT;
    DECLARE done INT DEFAULT FALSE;
    DECLARE tableNamesCursor CURSOR FOR 
    SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = "your_schema";
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SET result = "";
    OPEN tableNamesCursor;
    tableLoop: LOOP
        FETCH tableNamesCursor INTO tableName;
        IF done THEN 
            LEAVE tableLoop;
        END IF;

        IF REPLACE(tableName, "_deleted", "") != tableName THEN
            SET q = CONCAT("DROP TABLE ", tableName, ";");
            SET result = CONCAT(result, q, "\n");
        END IF;

        IF REPLACE(tableName, "_deleted", "") = tableName THEN            
            SET q = CONCAT("DROP TRIGGER ", tableName, "_delete;");
            SET result = CONCAT(result, q, "\n");
        END IF;
    END LOOP;

    SELECT result;
END

It creates SQL statements to remove the added tables and triggers. Here’s an example output again for my “people” table:

DROP TRIGGER people_delete;
DROP TABLE people_deleted;

Now that’s really it. Happy coding :)

---

Follow me on Twitter for more of my thoughts, articles, projects and work.