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. The 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.

Answer 3

Different way to achieve the same result. May not be really required for the question asked. But just for information.

1. Create a trigger on insert / update
2. Check if there is duplicate records found with current (NEW) records values.
   a. This can be checked by counting dupicates or checking of OTHER records exists with the same values, but different primary key
3. If found raise a Signal to throw an error

This is best suited if your condition is complex to decide uniqueness. Also consider the performance cost.

Sample

DELIMITER $$

CREATE TRIGGER `my_trigger` BEFORE INSERT/UPDATE
    ON `usertable`
    FOR EACH ROW BEGIN

    IF EXISTS (SELECT 1 FROM usertable WHERE userid <> NEW.userid AND username = NEW.username AND isactive = 1) THEN 
        SELECT CONCAT(NEW.username, ' exists !') INTO @error_text; 



     SIGNAL SQLSTATE '45000' SET message_text = @error_text; 
    END IF;
    END$$

DELIMITER ;

Answer 4

I would just create another (non-unique) field called FORMER_NAME and move the original name to that field when a user goes inactive. No need for a special uniqueness constraint that's not possible.

Answer 5

Nope, if there is a unique index (hence the name) you can not have duplicates. Either add a extra column to make each record unique. Or change the value so its unique.

Not recommended but for example you could add a timestamp "USER DELETED 2013/08/17:233805"

Answer 6

This is my solution when I met a similar problem:

add a column inactive, so the unique key as: (username,inactive)

for inactive, inactive = 0 means a user is active, inactive > 0 means a user is active

when deactivate a user, just set inactive = user_id, not 1 as we usually did!

now it allows duplicated usernames for inactive users, but only unique usernames for active users.

Answer 7

I expanded on @gordon-linoff answer by adding a generated column which provides the nullable functionality. I would rather have a true not null active column that has a definitive true and false value I can use to read and write that is not confusing and won't get messed up by accidentally forgetting about this null behavior later on when writing code. So I compute a column with a specialized name and then use that value in the constraint, so I get the nullable unique active behavior but can use the active column as I wish.

isactive BOOL NOT NULL,
_isactive_constraint_key_ BOOL AS (CASE WHEN isactive IS true THEN true END),
CONSTRAINT active_user UNIQUE(username, _isactive_constraint_key)