Here in Brazil we have a personal identifier called CPF. This identifier has a sequence of 11 digits and is unique for each Brazilian citizen. Possibly in other countries there is something like that too.
How is a unique identifier, I chose to set it in the database like a UNIQUE KEY. And now that things get complicated. A user could be registered by an administrator and be removed by some kind of problem. However, it is not actually removed, but hidden by a deleted flag. And in another moment, he could be recreated again (there is a need to recreate it, and not re-enable it).
Because it is an individual identifier, the CPF would be reused on recreated user and would conflict in UNIQUE KEY.
In this situation, I thought of some practical solutions to solve the problem as well as its disadvantages. But I like what is the most appropriate way to solve this kind of problem.
- Do not use a UNIQUE KEY. The disadvantage is that it could increase the query time, which could perhaps be solved by a KEY-only column.
- Set the column to NULL on pseudo-DELETE. The disadvantage is that I lose the information, however, could solve just creating a second column to store its value before being annulled.
- The UNIQUE KEY could group the column with a date removal column (NULL by default). The disadvantage is having to use two columns for each group UNIQUE KEY.
- Create a second table to store data removed without UNIQUE KEY. The disadvantages here are almost incalculable!
- Remove the row, rather than hide it. The disadvantage here is that I could lose the references in a user FK.
Currently I prefer the first option, and if very necessary, I would opt for the second or third option. But I wonder if there is a more correct way, following the concept more accepted by the community.