how to store deleted rows in a transactional database

Question

This is a follow-up to the comment raised by heximal on one of my other posts. I want to have a deleted_on column to detect deleted records to which heximal suggested that this column is redundant.

Here is his comment:

You're going to use deleted_xx fields to determine that the record is deleted?

imho, the best and most nice way is to add to record some activity attribute of boolean datatype (e.g. the field named ACTIVE). So in order to "delete" record, we must update the value of ACTIVE field and update_date, updated_by in single UPDATE query. In order to select all active records we just have to make query like:

SELECT * FROM MyTable WHERE ACTIVE=1

I know that Oracle Applications use such approach, and I agree

I also read the following posts:

• Store deleted rows in a table
• http://www.udidahan.com/2009/09/01/dont-delete-just-dont/
• http://ayende.com/Blog/archive/2009/08/30/avoid-soft-deletes.aspx

My question is: How to set the unique constraint on a table which has a isActive flag as suggested by heximal above. I do have surrogate keys in all my tables. But I want to ensure the natural key columns (what we call business key columns) have a unique constraint.

If I have a deleted_on field to track deletes, then I can include this column as part of the natural key constraint. So, it allows more than one deleted record with the same business key combination, differing only in the deleted_on date field.

If I have isActive field and use last_updated_on column to track the deletion date, I have to 2 options on the natural key constraint

1. I can include isActive as part of my natural key constraint. But this would allow max only one deleted record with the same business key combination.
2. I can include isActive plus last_updated_on as part of natural key constraint. But I see having an extra column deleted_on makes it easier.

Any thoughts? Am I missing something here?

Answer 1

"My question is how to set the unique constraint on a table which has a isActive flag as suggested by heximal above. I do have surrogate keys in all my tables. But I want to ensure the natural key columns (what we call business key columns) have a unique constraint.

If I have a deleted_on field to track deletes, then I can include this column as part of the natural key constraint. So, it allows more than one deleted record with the same business key combination, differing only in the deleted_on date field."

Even with a deleted_on DATE field as part of your natural key, you still couldn't delete-reinsert-delete all on the same day. May seem pathological, but can you really be certain that the pathological case will NEVER occur ?

If your database needs to reflect the fact that some of the content is "active" in the sense that it is highly relevant for current business, and some other content is "inactive", e.g. in the sense that the only reason for keeping it some time is for archival purposes, then design your database to reflect that fact by defining two tables: one with the "active" content and one with the "archived" content. Perhaps you can use a trigger to automatically achieve the "move-to-archive" whenever a delete occurs.

Doing so, you can have your natural key enforced by the DBMS using a unique constraint on the "active" table, and you can include a deletion-timestamp in the "archive" table, and you may not even need to define any key at all on that table.