设置表关系做什么“级联"、“置空"和“限制"做? [英] Setting up table relations what do "Cascade", "Set Null" and "Restrict" do?

问题描述

我想开始在新项目中使用表关系.

I want to start using table relations in a new project.

经过一些谷歌搜索后，我将 2 个表设置为 InnoDB:

After some googling I got 2 tables set up as InnoDB:

我要链接的键是

->users->userid(主要)->sessions->userid(索引)

->users->userid (primary) ->sessions->userid (index)

在这个过程中我唯一不明白的是更新时"和删除时"的不同设置是什么

The only thing that I don't understand in this process is what the different settings for "On update" and "On delete" do

这里的选项是:

• --(什么都没有?)
• 级联 (???)
• Set Null(将所有内容都设置为 null?)
• 没有任何动作(好吧……)
• 限制 (???)

我基本上希望在用户完全删除时删除会话中的数据这是因为只有在我的会话管理器检测到过期时才会删除会话...

I basically want the data in sessions to be deleted when a user is completely deleted This since the sessions will only be deleted when the expiration is detected by my session manager...

因此，如果有人能告诉我这些选项的作用，我们将不胜感激.

So if anyone can tell me what these options do it would be much appreciated.

推荐答案

CASCADE 将在父更改时传播更改.(如果删除一行，约束表中引用该行的行也将被删除等)

CASCADE will propagate the change when the parent changes. (If you delete a row, rows in constrained tables that reference that row will also be deleted, etc.)

SET NULL 在父行消失时将列值设置为 NULL.

SET NULL sets the column value to NULL when a parent row goes away.

RESTRICT 导致尝试删除父行失败.

RESTRICT causes the attempted DELETE of a parent row to fail.

您没有询问它们，但 SQL 标准定义了另外两个操作:SET DEFAULT 和 NO ACTION.在 MySQL 中，NO ACTION 等价于 RESTRICT.(在某些 DBMS 中，NO ACTION 是延迟检查，但在 MySQL 中所有检查都是立即进行的.)MySQL 解析器接受 SET DEFAULT，但 InnoDB 和 NDB 引擎都拒绝那些语句，所以 SET DEFAULT 实际上不能用于 ON UPDATE 或 ON DELETE 约束.

You didn't ask about them, but the SQL standard defines two other actions: SET DEFAULT and NO ACTION. In MySQL, NO ACTION is equivalent to RESTRICT. (In some DBMSs, NO ACTION is a deferred check, but in MySQL all checks are immediate.) The MySQL parser accepts SET DEFAULT, but both the InnoDB and NDB engines reject those statements, so SET DEFAULT can't actually be used for either an ON UPDATE or ON DELETE constraint.

另外，请注意级联外键操作不会激活 MySQL 中的触发器.

Also, note that cascading foreign key actions do not activate triggers in MySQL.

这篇关于设置表关系做什么“级联"、“置空"和“限制"做?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！