具有软删除、唯一键和外键约束的 MySQL

Question

假设我有两个表，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 列出所有内容，我可以隐藏所有已删除的记录，直到我需要它们。

到目前为止一切顺利。

问题来了:

• 用户使用用户名(例如“Sam”)注册，
• 我软删除了该用户(出于不相关的原因)，并且
• 其他人以 Sam 的身份注册，突然我们违反了对 user 的 UNIQUE 约束。

我希望用户能够编辑自己的用户名，所以我不应该将 username 设为主键，删除用户时我们仍然会遇到同样的问题。

有什么想法吗？

编辑澄清:在下面添加了 RedFilter 的回答和评论。

我担心“已删除”的用户和评论对公众不可见，但只有管理员可见，或者为了计算统计数据而保留。

这个问题是一个思想实验，用户和评论表只是示例。尽管如此，username 并不是最好的选择。 RedFilter 对用户身份提出了有效的观点，尤其是当记录在公共(public)环境中呈现时。

关于“为什么用户名不是主键？”:这只是一个示例，但如果我将其应用于实际问题，我将需要在假设存在代理主键的现有系统。

Answer 1

在字段上添加唯一约束(用户名，已删除)将“已删除”的字段类型更改为 INTEGER。

在删除操作期间(可以在触发器中完成，或者在您需要实际删除用户的部分代码中)将 id 字段的值复制到已删除的字段。

此方法允许您:

• 为活跃用户保留唯一名称(删除 = 0)
• 允许多次删除具有相同用户名的用户

“已删除”字段不能只有 2 个值，因为以下情况将不起作用:

1. 您创建用户“Sam”
2. 删除用户 Sam
3. 您使用用户名“Sam”创建新用户
4. 您尝试删除用户名为“Sam”的用户 - 失败。您已经有记录 userName = 'Sam' and deleted = '1'