MySQL с мягким удалением, уникальными ключами и внешними ключами

Скажем, у меня две таблицы, user и comment. У них есть определения таблиц, которые выглядят так:

CREATE TABLE `user` (
  `id`       INTEGER NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(255) NOT NULL,
  `deleted`  TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY (`username`)
) ENGINE=InnoDB;

CREATE TABLE `comment` (
  `id`      INTEGER NOT NULL AUTO_INCREMENT,
  `user_id` INTEGER NOT NULL,
  `comment` TEXT,
  `deleted` TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_comment_user_id` FOREIGN KEY (`user_id`)
    REFERENCES `user` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=InnoDB;

Это отлично подходит для обеспечения целостности данных и всего этого, но я хочу, чтобы иметь возможность "удалять" пользователя и хранить все его комментарии (для справки).

С этой целью я добавил deleted, чтобы я мог SET deleted = 1 записать. Перечисляя все с помощью deleted = 0 по умолчанию, я могу скрыть все удаленные записи, пока они мне не понадобятся.

Пока все хорошо.

Проблема возникает, когда:

Пользователь подписывается с именем пользователя (скажем, "Сэм" ),
Я мягко удаляю этого пользователя (по независящим причинам) и
Кто-то еще приходит, чтобы зарегистрироваться как Сэм, и внезапно мы нарушили ограничение UNIQUE на user.

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

Любые мысли?

Изменить для пояснения: Добавлены следующие ответы и комментарии RedFilter ниже.

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

Этот вопрос представляет собой мысленный эксперимент, при этом таблицы пользователей и комментариев просто являются примерами. Тем не менее, username был не лучшим для использования; RedFilter делает достоверные данные о личности пользователя, особенно когда записи представлены в общедоступном контексте.

Относительно "Почему имя пользователя не является первичным?": это всего лишь пример, но если я применил это к реальной проблеме, мне нужно будет работать в рамках ограничений существующей системы, предполагающей существование суррогатный первичный ключ.

Ответы

Ответ 1

Добавить уникальное ограничение на поля (имя пользователя, удаленный) Измените тип поля для "удаленных" на INTEGER.

Во время операции удаления (это можно сделать в триггере или в части кода, где вам действительно нужно удалить пользователя) копировать значение поля id в удаленное поле.

Этот подход позволяет вам:

сохранить уникальные имена для активных пользователей (удалено = 0)
разрешить пользователям с одинаковым именем пользователя несколько раз

Поле 'Deleted' не может иметь только 2 значения, потому что следующий сценарий не будет работать:

вы создаете пользователя 'Sam'
Пользователь Sam удален.
Вы создаете нового пользователя witn userName 'Sam'
Вы пытаетесь удалить пользователя с именем пользователя 'Sam' - сбой. У вас уже есть запись userName = 'Sam' и deleted = '1'

Ответ 2

Просто сохраните уникальный индекс или contraint на username. Вы не хотите, чтобы новые пользователи могли использовать удаленное имя, поскольку не только может быть общая путаница в отношении личности, но если вы по-прежнему показываете старые сообщения от удаленного пользователя, то они по ошибке будут считаться опубликованными новый пользователь с тем же именем.

Когда регистрируется новый пользователь, вы обычно проверяете, используется ли имя, прежде чем разрешить регистрацию для завершения, поэтому здесь не должно быть конфликтов.

Ответ 3

Моим практическим решением для мягкого удаления является архивирование путем создания новой таблицы со следующими столбцами: original_id, table_name, payload и (необязательный первичный ключ 'id).

Где original_id - исходный идентификатор удаленной записи, table_name - имя таблицы удаленной записи (в вашем случае "user"), payload - строка в формате JSON из всех столбцов удаленной записи.

Я также предлагаю сделать указатель на столбец original_id для последующего извлечения данных.

Таким способом архивирования данных. У вас будут эти преимущества

Следите за всеми данными в истории
Имеется только одно место для архивирования записей из любой таблицы, независимо от структуры таблицы удаленных записей
Не беспокойтесь об уникальном индексе в исходной таблице
Не беспокойтесь о проверке внешнего индекса в исходной таблице

Это уже обсуждение здесь, объясняющее, почему софт-удаление не является хорошей идеей на практике. Soft-delete представляет некоторые потенциальные проблемы в будущем, такие как подсчет записей,...