MySQL 的默认 ON DELETE 行为是什么? [英] What is MySQL's default ON DELETE behavior?

问题描述

我正在尝试解析 MySQL 文档.他们可能会更清楚.他们似乎在说有五种可能性:SET NULL、NO ACTION、RESTRICT、CASCADE 和 SET DEFAULT.

I'm trying to parse the MySQL docs. They could be clearer. What they seem to be saying is that there are five possibilities: SET NULL, NO ACTION, RESTRICT, CASCADE, and SET DEFAULT.

NO ACTION 和 RESTRICT 做同样的事情(防止任何破坏 FK 的 DB 更改)，并且该事情是默认设置，因此如果您省略 ON DELETE 子句，则您说的是 NO ACTION(或 RESTRICT - 同样的事情).

NO ACTION and RESTRICT do the same thing (prevent any DB change that breaks an FK) and that thing is the default so if you omit an ON DELETE clause you're saying NO ACTION (or RESTRICT -- same thing).

SET NULL 允许删除父行，将 FK 设置为 NULL.

SET NULL allows a parent row deletion, sets the FK to NULL.

CASCADE 删除子行.

CASCADE deletes child row.

SET DEFAULT 不应该被使用.

SET DEFAULT should just never be used.

这或多或少是正确的吗?

Is this more or less correct?

推荐答案

是的，是正确的:

无操作:[...] InnoDB拒绝删除或更新操作用于父表.

NO ACTION: [...] InnoDB rejects the delete or update operation for the parent table.

RESTRICT:拒绝删除或更新父表的操作.指定 RESTRICT(或 NO ACTION)是与省略 ON DELETE 或ON UPDATE 子句.[...]

RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause. [...]

显然 NO ACTION 和 RESTRICT 是同义词.此外，由于它们在没有 ON DELETE/UPDATE 子句时使用，这是默认行为.

Apparently NO ACTION and RESTRICT are synonymous. Additionally, since they are used whenever there is no ON DELETE / UPDATE clause, this is the default behavior.

SET NULL:从父表中删除或更新行并设置外键列或列子表为NULL.[...]

SET NULL: Delete or update the row from the parent table and set the foreign key column or columns in the child table to NULL. [...]

外部列设置为 NULL，前提是它没有声明为 NOT NULL(否则 InnoDB 将不允许删除或更新).

The foreign column is set to NULL, provided it is not declared as NOT NULL (or InnoDB will not allow deletion or update).

CASCADE:从父表中删除或更新行并自动删除或更新子表中的匹配行.[...]

CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. [...]

级联删除(或更新)外部列.

Cascade deletes (or updates) the foreign column.

SET DEFAULT:识别此操作由解析器，但 InnoDB 拒绝包含 ON DELETE 的表定义SET DEFAULT 或 ON UPDATE SET DEFAULT子句.

SET DEFAULT: This action is recognized by the parser, but InnoDB rejects table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

所以基本上你不能使用那个选项.

So basically you cannot use that option.

这篇关于MySQL 的默认 ON DELETE 行为是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！