CODEWITHSUNDEEP
mysqlunique-constraint
user2692862
user2692862

Reputation: 321

Can I conditionally enforce a uniqueness constraint?

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?

Upvotes: 32

Views: 15555

Answers (8)

giang le huynh
giang le huynh

Reputation: 11

Assume table [user] has 2 columns user_name and is_deleted_fg, conditionally enforce:

ALTER TABLE user
ADD CONSTRAINT uc_active_username 
UNIQUE (user_name, (CASE WHEN is_deleted_fg = '1' THEN NULL ELSE is_deleted_fg END))

Upvotes: 0

Roger
Roger

Reputation: 7610

Nope, if there is a unique index (hence the name) you cannot have duplicates. Either add an extra column to make each record unique. Or change the value so it's unique.

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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271023

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.

Upvotes: 74

Galen Howlett
Galen Howlett

Reputation: 694

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)

Upvotes: 0

leungxd
leungxd

Reputation: 526

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.

Upvotes: 0

MiguelMunoz
MiguelMunoz

Reputation: 4962

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.

Upvotes: 1

Akhil
Akhil

Reputation: 2602

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 ;

Upvotes: 2

spencer7593
spencer7593

Reputation: 108500

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.

Upvotes: 10

Related Questions