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身份注册，突然我们违反了user上的UNIQUE约束。

我希望用户能够编辑自己的用户名，所以我不应该让username主键和删除用户的时候我们仍然有同样的问题。

有什么想法？

编辑澄清：添加以下RedFilter的答案，下面的评论。

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

这个问题是一个思想实验，用户和评论表仅仅是示例。尽管如此，username并不是最好的选择; RedFilter提供有关用户身份的有效信息，特别是当记录显示​​在公共场合时。

关于“为什么不是用户名的主键？”：这只是一个例子，但如果我申请这一个真正的问题，我会需要到它假定现有系统的约束下工作代理主键的存在。

Answer 1

为字段（用户名，删除）添加唯一约束 将'deleted'的字段类型更改为INTEGER。

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

这种方法允许你：

• 保持唯一的名称为活跃用户（删除= 0）
• 允许使用相同的用户名删除用户多次

字段 '删除' 不能只有2个值，因为以下场景不起作用：

1. 您创建用户'Sam'
2. 用户Sam被删除
3. 你创建新的用户WITN用户名“萨姆”
4. 您尝试删除用户帐号“萨姆” - 失败。你已经有记录userName ='Sam'并删除='1'

Answer 2

只需保留username上的唯一索引或约束。您不希望新用户能够使用已删除的姓名，因为不仅可能会对身份造成普遍的混淆，但是如果您仍然显示已删除用户的旧帖子，则他们将被错误地理解为由具有相同名称的新用户。

当新用户注册时，您通常会在允许注册完成之前检查名称是否正在使用，因此这里不应该有冲突。

。