具有软删除，唯一键和外键约束的MySQL [英] MySQL with Soft-Deletion, Unique Key and Foreign Key Constraints

问题描述

说我有两个表，user和comment.它们具有如下表定义:

Say I have two tables, user and comment. They have table definitions that look like this:

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;

这对于强制执行数据完整性及所有其他功能非常有用，但我希望能够删除"用户并保留其所有注释(以供参考).

This is great for enforcing data integrity and all that, but I want to be able to "delete" a user and keep all its comments (for reference's sake).

为此，我添加了deleted，以便可以对记录进行SET deleted = 1.通过默认使用deleted = 0列出所有内容，我可以隐藏所有已删除的记录，直到需要它们为止.

To this end, I've added deleted so that I can SET deleted = 1 on a record. By listing everything with deleted = 0 by default, I can hide away all the deleted records until I need them.

到目前为止一切都很好.

So far so good.

问题出现在以下时间:

• 用户使用用户名(例如"Sam")进行注册，
• 出于不相关的原因，我软删除了该用户，并且
• 其他人来注册成为Sam，突然之间我们违反了user上的UNIQUE约束.

• A user signs up with a username (say, "Sam"),
• I soft-delete that user (for unrelated reasons), and
• Someone else comes along to sign up as Sam, and suddenly we've violated the UNIQUE constraint on user.

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

I want users to be able to edit their own usernames, so I shouldn't make username the primary key, and we'll still have the same problem when deleting users.

有什么想法吗?

编辑以进行澄清:在RedFilter的回答和评论下方添加了此内容.

Edit for clarification: Added following RedFilter's answer and comments below.

我担心删除的"用户和评论对公众不可见，而仅对管理员可见，或者为了计算统计信息而保留.

I'm concerned with the case where the "deleted" users and comments are not visible to the public, but are visible only administrators, or are kept for the purpose of calculating statistics.

这个问题是一个思想实验，用户和注释表仅是示例.不过，username并不是最好的使用方式. RedFilter提出了有关用户身份的有效观点，特别是当记录是在公共环境中呈现时.

This question is a thought experiment, with the user and comment tables just being examples. Still, username wasn't the best one to use; RedFilter makes valid points about user identity, particularly when the records are presented in a public context.

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

Regarding "Why isn't username the primary key?": this is just an example, but if I apply this to a real problem I'll be needing to work within the constraints of an existing system that assumes the existence of a surrogate primary key.

推荐答案

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

Add unique constraint on fields(username, deleted) Change field type for 'deleted' to INTEGER.

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

During delete operation (it can be done in trigger, or in part of code where you need actually delete user) copy value of id field to deleted field.

此方法可让您:

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

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

Field 'Deleted' can't have only 2 value because the following scenario will not work:

1. 您创建用户"Sam"
2. 用户Sam被删除
3. 您使用userName'Sam'创建新用户
4. 您尝试删除用户名为"Sam"的用户-失败.您已经有记录userName ='Sam'并且已删除='1'

这篇关于具有软删除，唯一键和外键约束的MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！