MySQL with Soft-Deletion, Unique Key and Foreign Key Constraints

Add unique constraint on fields(username, deleted) Change field type for 'deleted' to INTEGER.

During delete operation (it can be done in trigger, or in part of code where you need actually delete user) copy value of id field to deleted field.

This approach allow you:

• keep unique names for active users (deleted = 0)
• allow delete users with same username several times

Field 'Deleted' can't have only 2 value because the following scenario will not work:

1. you create user 'Sam'
2. User Sam is deleted
3. You create new user witn userName 'Sam'
4. You try delete user with userName 'Sam' - fail. You already have record userName = 'Sam' and deleted = '1'

Just keep the unique index or contraint on username. You do not want new users to be able to use the deleted name, as not only could there be general confusion about identity, but if you are still showing the old posts from the deleted user, then they will mistakenly be understood to be posted by the new user with the same name.

When a new user registers, you would normally check to see if the name is in use before allowing registration to complete, so there should be no conflict here.

.