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

本文介绍了具有软删除、唯一键和外键约束的 MySQL的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

假设我有两个表，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.

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

问题描述

推荐答案

相关文档推荐