MySQL 设置表关系“Cascade”、“Set Null”和“Restrict”有什么作用？

Setting up table relations what do "Cascade", "Set Null" and "Restrict" do?

提问by HTDutchy

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

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

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

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

The keys I want to link are

我要链接的键是

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

->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

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

The options here are:

这里的选项是：

• -- (nothing?)
• Cascade (???)
• Set Null (sets everything to null?)
• No action (well duh...)
• Restrict (???)

• - （没有？）
• 级联（？？？）
• 设置为空（将所有内容都设置为空？）
• 没有行动（好吧，废话...）
• 严格 （？？？）

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.

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

回答by Ted Hopp

CASCADEwill 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.)

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

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

SET NULL当父行消失时，将列值设置为 NULL。

RESTRICTcauses the attempted DELETE of a parent row to fail.

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

EDIT: You didn't ask about them, but the SQL standard defines two other actions: SET DEFAULTand NO ACTION. In MySQL, NO ACTIONis equivalent to RESTRICT. (In some DBMSs, NO ACTIONis 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 DEFAULTcan't actually be used for either an ON UPDATEor ON DELETEconstraint.

编辑：您没有询问它们，但 SQL 标准定义了另外两个操作：SET DEFAULT和NO ACTION. 在 MySQL 中，NO ACTION相当于RESTRICT. （在某些 DBMS 中，NO ACTION是延迟检查，但在 MySQL 中，所有检查都是立即进行的。） MySQL 解析器接受SET DEFAULT，但 InnoDB 和 NDB 引擎都拒绝这些语句，因此SET DEFAULT实际上不能用于 an ON UPDATEorON DELETE约束。

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

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

回答by Jay Patel

The table containing the foreign key is called the referencingor child table, and the table containing the candidate key is called the referencedor parent table.

包含外键的表称为引用表或子表，包含候选键的表称为被引用表或父表。

Set NULL: Sets the column value to NULLwhen you delete the parent table row.

CASCADE: CASCADE will propagate the changewhen the parent changes. If you delete a row, rows in constrained tables that reference that row will also be deleted, etc.

RESTRICT: RESTRICT causes you can not delete a given parent rowif a child rowexists that references the value for that parent row.

NO ACTION: NO ACTION and RESTRICT are very much alike. when an UPDATE or DELETE statement is executed on the referenced table, the DBMS verifies at the end of the statement execution that none of the referentialrelationships are violated. in short child row no concern if parent row deleteor update.

Set NULL：删除父表行时将列值设置为NULL。

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

RESTRICT：限制的原因，你不能删除一个给定的父行，如果一个子行存在引用，该父行的值。

NO ACTION：NO ACTION 和 RESTRICT 非常相似。当对引用表执行 UPDATE 或 DELETE 语句时，DBMS 在语句执行结束时验证没有违反任何引用关系。简而言之，如果父行删除或更新，则无需担心子行。