帮助删除关系表
我有四个表：
users: id, thread_idthreads: id, language_idposts: id, user_id, language_idlanguages>：id
USERS.thread_id 是 THREADS.id 的外键，POSTS.user_id 是 USERS.id 的外键，POSTS.language_id 是 LANGUAGES.id 的外键。
我无法删除用户，因为 POSTS.user_id 会引发外键约束错误，并且我无法删除帖子，因为即使删除用户，我也希望所有帖子（和线程）都可读。
我应该怎么办？
I have four tables:
users: id, thread_idthreads: id, language_idposts: id, user_id, language_idlanguages: id
USERS.thread_id is a foreign key to THREADS.id, POSTS.user_id is foreign key to USERS.id and POSTS.language_id foreign key to LANGUAGES.id.
I can't delete an user because the POSTS.user_id will throw a foreign key constraint error, and I can't delete the post because I want all the posts (and threads) to be readable there even if the user is deleted.
What should I do?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这称为软删除，您可以在 SO 上看到它的工作原理。当您看到“已删除”用户的答案时，他们会显示为灰色。
将用户保留在数据库中，但添加一个标志列
isDeleted，您在删除用户时设置该标志列。然后（显然）禁止该用户的任何登录并（可选）专门显示它们。
This is known as a soft delete and you can see it at work right here on SO. When you see an answer from a 'deleted' user, they're grayed out.
Keep the user in the database but add a flag column
isDeletedwhich you set when the user is deleted.Then (obviously) disallow any logins for that user and (optionally) display them specially.
看起来一切都按设计工作:) 如果 MySQL 让您删除用户，同时保留 posts.user_id 指向它，您的数据库将变得不一致。
如果您希望已删除用户的帖子可读，请在删除用户之前将其 user_id 重置为其他内容（0？NULL？）。
如果您也希望保留用户信息，那么您显然无法删除用户行。您应该添加某种“用户已删除”列来更改用户在 UI 上的显示方式。
Looks like everything is working as designed :) If MySQL let you delete the user while leaving posts.user_id pointing to it, your DB would become inconsistent.
If you want posts from deleted users to be readable, reset their user_id to something else (0? NULL?) before deleting the user.
If you want the user info to remain too, then you obviously can't delete the user row. You should add some kind of 'user is deleted' column that changes how the user is shown on the UI.
外键用于强制数据完整性。由于您有理由在没有有效用户 ID 的情况下存在帖子和线程，因此您实际上并不需要外键数据完整性。
我要么完全删除外键，要么利用 外键创建子句。当引用的外部值更改时，您可以使用 MySQL
CASCADE、RESTRICT或SET NULL。在这种情况下，您可以使用
ON DELETE SET NULL创建外键，并且当您删除用户时，用户 ID 将在您的 posts 表中设置为 NULL。默认情况下，外键是使用RESTRICT创建的，这就是为什么您无法删除用户并在 posts 表中留下孤立值的原因。Foreign keys are for enforcing data integrity. Since you have a reason to have posts and threads exist without a valid user id, then you don't really need the foreign key data integrity.
I would either remove the foreign key entirely, or utilize the
ON DELETEportion of a foreign key creation clause. You can have MySQLCASCADE,RESTRICT, orSET NULLwhen a referenced foreign value changes.In this case, you could create the foreign key with
ON DELETE SET NULL, and the user id would be set to NULL in your posts table when you delete a user. Foreign keys are created withRESTRICTby default, which is why you can't delete the user and leave an orphaned value in the posts table.实际上不要向表中添加外键限制。它们没有必要。没有他们，你就可以自由地做任何你想做的事。仅在必要时添加它们。
Don't actually add foreign key restraints to your tables. They aren't necessary. Without them you're free to do whatever you want. Only add them if they are necessary.