SQL Schema design question - delete flags

Question

in our database schema, we like to use delete flags. When a record is deleted, we then update that field, rather than run a delete statement. The rest of our queries then check for the delete flag when returning data.

Here is the problem:

The delete flag is a date, with a default value of NULL. This is convenient because when a record is deleted we can easily see the date that it was deleted on.

However, to enforce unique constraints properly, we need to include the delete flag in the unique constraint. The problem is, on MS SQL , it behaves in accordance to what we want (for this design), but in postgresql, if any field in a multi column unique constraint is NULL, it allows the field. This behavior fits the SQL standard, but it makes our design broken.

The options we are considering are:

1. make a default value for the deleted field to be some hardcoded date

2. add a bit flag for deleted, then each table would have 2 delete related fields - date_deleted and is_deleted (for example)

3. change the date_deleted to is_deleted (bit field)

I suspect option 1 is a performance hit, each query would have to check for the hardcoded date, rather than just checking for IsNUll. Plus it feels wrong.

Option 2, also, feels wrong - 2 fields for "deleted" is non-dry.

Option 3, we lose the "date" information. There is a modified field, which would, in theory reflect the date deleted, but only assuming the last update to the row was the update to the delete bit.

So, Any suggestions? What have you done in the past to deal with "delete flags" ?

Update Thanks to everyone for the super quick, and thoughtful responses. We ended up going with a simple boolean field and a modified date field (with a trigger). I just noticed the partial index suggestion, and that looks like the perfect solution for this problem (but I havent actually tried it)

Answer 1

If just retaining the deleted records is important to you, have you considered just moving them to a history table?

• This could easily be achieved with a trigger.
• Application logic doesn't need to account for this deleted flag.
• Your tables would stay lean and mean when selecting from it.
• It would solve your problem with unique indexes.

Answer 2

Option 3, we lose the "date" information. There is a modified field, which would, in theory reflect the date deleted, but only assuming the last update to the row was the update to the delete bit.

Is there a business reason that the record would be modified after it was deleted? If not, are you worrying about something that's not actually an issue? =)

In the system I currently work on we have the following "metadata" columns _Deleted, _CreatedStamp, _UpdatedStamp, _UpdatedUserId, _CreatedUserId ... quite a bit, but it's important for this system to carry that much data. I'd suggest going down the road of having a separate flag for Deleted to Modified Date / Deleted Date. "Diskspace is cheap", and having two fields to represent a deleted record isn't world-ending, if that's what you have to do for the RDBMS you're using.