Conditional uniqueness

Working with Data
1

Let’s say I have a unique index on a field, or combination of fields:

{recordId:1}, {unique:true}

However, I also have a field “isDeleted: boolean”. If isDeleted is true, I don’t want this object to be considered by the uniqueness constraint. In other words, there can only be 1 record for a given recordId that has isDeleted:false.

Is there a way to express this?

One alternative that was suggested was instead of having isDeleted be a boolean, make it a random string. I’m wondering if there is a more elegant way.

2

I can see 2 or 3 ways.

  1. Add isDeleted to your index.
  2. Use a partial index that exclude isDeleted:true
  3. Rather than setting isDeleted true and polluting your working set with deleted documents, move them is another collection if you are really interested in historical data.