Могу ли я условно применять ограничение уникальности?

Моя база данных содержит таблицу пользователей. У каждого активного пользователя есть уникальное имя пользователя. Я хотел бы иметь возможность деактивировать пользователя и освобождать имя пользователя, которое они используют, но держать их в одной таблице.

Есть ли способ только принудительно установить ограничение единственности?

Ответ 1

Добавьте еще один столбец с именем isactive. Создайте уникальное ограничение на (username, isactive).

Тогда вы можете одновременно иметь как активное, так и неактивное имя пользователя. Вы не сможете иметь два активных имени пользователя.

Если вы хотите несколько неактивных имен, используйте NULL для значения isactive. Значения NULL могут быть повторены в уникальном индексе.

Ответ 2

Нет, ограничение UNIQUE не может быть "условным".

Один параметр - установить столбец username равным NULL. Ограничение UNIQUE позволит использовать несколько строк со значением NULL.

Вы можете перевести это на любую строку, которую хотите отобразить. либо в приложении, либо в SQL

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

Если вы сохраняете эти строки для целей истории/архива, вы, вероятно, НЕ хотите обновлять столбец username. (Если вы измените значение столбца username, то последующему оператору будет разрешено вставлять строку с тем же значением, что и предыдущее имя пользователя.)

Вместо этого вы можете добавить дополнительный столбец в свою таблицу, чтобы представить условие "пользователь удалил". Например:

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

Вы можете проверить этот столбец и вернуть константу 'USER DELETED' вместо столбца имени пользователя всякий раз, когда установлено значение user_deleted boolean:

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

(Используйте значение 1 для указания логического состояния "пользователь удалено".)

Большое преимущество этого подхода заключается в том, что колонку username НЕ нужно изменять, значение username и ограничение UNIQUE предотвратит вставку новой строки с дублирующимся именем пользователя.

Ответ 3

Нет, если есть уникальный индекс (отсюда и название), у вас не может быть дубликатов. Либо добавьте дополнительный столбец, чтобы сделать каждую запись уникальной. Или измените значение так, чтобы оно было уникальным.

Не рекомендуется, но, например, вы можете добавить временную метку "USER DELETED 2013/08/17: 233805"

Ответ 4

Разный способ добиться того же результата. Не может быть действительно необходимо для заданного вопроса. Но только для информации.

Создать триггер при вставке/обновлении
Проверьте наличие дубликатов записей с текущими (NEW) значениями записей.
а. Это можно проверить путем подсчета дубликатов или проверки ДРУГИХ записей с одинаковыми значениями, но с использованием другого первичного ключа
Если обнаружено повышение Signal, чтобы выбросить ошибку

Это лучше всего подходит, если ваше состояние сложно решить уникальность. Также рассмотрим стоимость исполнения.

Пример

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 ;