具有软删除、唯一键和外键约束的 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.

到目前为止一切顺利.

问题来了:

• 用户使用用户名(例如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. 您使用用户名Sam"创建新用户
4. 您尝试删除用户名为Sam"的用户 - 失败.您已经有记录 userName = 'Sam' and deleted = '1'

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