Right now my user_names are unique. I'd like to add support for reusing user names, but I don't want to delete the old users because there are foreign keys that reference user_id.
What can I add to this table to allow reuse of user_names while leaving the old ones in there? Looking up "active" users needs to be an indexed operation, preferably a unique scan. I had considered adding an is_active column, but:
I can't enforce it via unique key on (user_name,is_active) because is_active can have multiple "false" values.
I can't check it with a trigger because the table is mutating.
Surely this is a common problem, I just don't really know what to google for.
I wonder if using a separate index on (user_name,is_active) would even make a difference for lookups. I don't expect this to happen a lot, so there usually will only be one row per user_name anyway. I guess I'll do some tests when I get some real data to test with.