Can I conditionally enforce a uniqueness constraint?

Question

My database contains a table of users. Every active user has a unique username. I'd like to be able to deactivate a user and free up the username they're using, but keep them in the same table.

Is there a way to only conditionally enforce the uniqueness constraint?

Answer 1

Add another column called something like isactive. Then create a unique constraint on (username, isactive).

Then you can have both an active and inactive user name at the same time. You will not be able to have two active user names.

If you want multiple inactive names, use NULL for the value of isactive. NULL values can be repeated in a unique index.

Answer 2

No, a UNIQUE constraint can't be "conditional".

One option is to set the username column to NULL. The UNIQUE constraint will allow multiple rows with NULL value.

You could translate that to any string you wanted for display. either in the application, or in the SQL

SELECT IFNULL(t.username,'USER DELETED') AS username
  FROM mytable t

---

If you are retaining these rows for historical/archive purposes, you probably do NOT want to update the username column. (If you change the value of the username column, then a subsequent statement will be allowed to insert a row with the same value as the previous username.)

You could instead add an additional column to your table, to represent the "user deleted" condition. For example:

user_deleted TINYINT(1) UNSIGNED DEFAULT 0 COMMENT 'boolean' 

You could check this column and return the 'USER DELETED' constant in place of the username column whenever the user_deleted boolean is set:

SELECT IF(u.user_deleted,'USER DELETED',u.username) AS username

(Use a value of 1 to indicated a logical "user deleted" condition.)

The big advantage to this approach is that the username column does NOT have to be modified, the username value, and the UNIQUE constraint will prevent a new row with a duplicate username from being inserted.