This post describes a robust solution and other alternatives for having unique constraint at DB level for a table with soft deleted rows.
The system identifies the users by their mobile number and hence mobile number must be unique across users. The users are soft deleted in the system by updating column
deleted = 1. A new user can register in with same mobile number as previously deactivated user (since mobile numbers are recycled by telecoms). The unique check at application are susceptible to fail in case of concurrent requests, unique constraint is needed at DB to ensure integrity of data.
The solution should
- work for existing rows imported from legacy system
- work across different databases supported by product
We were able to find different flavors of solutions on net but they were incomplete for our case. They only served as starting point to a solution that meets all of our needs mentioned above.
The Final Solution
- Add a new column
- Add unique constraint for combination
- A new row added to table would have value of ‘NA’
deletion_token. This is ensured by setting up default value of
NAat DB level and having constructor of User model(used by ORM) to initialize
- Insert a random UUID for soft deleted
- On soft delete of user, generate new UUID and set it on
Path to the above solution
Add unique constraint for columns
mobile_number, deletedDrawback: This wouldn’t allow us to have more than one deleted user with same mobile number
Add a unique constraint with a where clause eg:
ADD CONSTRAINT .... WHERE deleted != 1;Drawback: The where clause in constraint definition is not supported by all databases
Instead of using only 0 or 1 as values for deleted column, increment the number on each delete. Drawback: Expensive as it needs extra db call to retrieve previously soft deleted rows and also expensive to update numbers for existing soft deleted rows in legacy system. It would theoretically fail for concurrent requests without lock.
Add a new time-stamp column called
deleted_atand add an unique constraint on
mobile_number, deleted_atDrawback: The old rows in legacy system didn’t have data for
deleted_atand populating with dummy data wasn’t acceptable.
Add a new column called
deletion_tokenand add a constraint on
mobile_number, deletion_tokenwith NULL value for new rows and UUID for soft deleted rows. Drawback: Few databases don’t consider nulls as equal and hence unique constraint does not fail for two rows with same mobile number and NULL value in
Slight modification to point 5, to arrive at the final solution described in the beginning of the post