何时使用"ON UPDATE CASCADE"

Question

我经常使用 ON DELETE CASCADE，但从未使用过 ON UPDATE CASCADE，因为我不太确定在什么情况下它会有用。

为了讨论，让我们看一些代码。

CREATE TABLE parent (
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);

CREATE TABLE child (
    id INT NOT NULL AUTO_INCREMENT, parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
);

对于ON DELETE CASCADE，如果一个具有id的父记录被删除，那么具有parent_id = parent.id的子记录将自动被删除。这应该不是问题。

1. 这意味着当父记录的id更新时，ON UPDATE CASCADE会执行相同的操作吗？

2. 如果（1）成立，这意味着如果父记录的parent.id不能更新（或永远不会被更新），例如当它为AUTO_INCREMENT或始终设置为TIMESTAMP时，则不需要使用ON UPDATE CASCADE。这正确吗？

3. 如果（2）不成立，在什么其他情况下应该使用ON UPDATE CASCADE？

4. 如果我因某种原因将child.parent_id更新为不存在的值，那么它是否会被自动删除？

好的，我知道，上面的一些问题可以通过编程测试来理解，但我也想知道其中是否依赖于数据库供应商。

请给出一些指导。

- NawaMan

---

参见：https://dev59.com/Umw05IYBdhLWcg3w_W5O - Lenik

Answer 1

如果您的主键只是一个自动增加的标识值，那么您实际上不需要使用 ON UPDATE CASCADE。

然而，假设您的主键是一个10位数字的UPC条形码，由于扩展需要将其更改为13位的UPC条形码。在这种情况下，ON UPDATE CASCADE将允许您更改主键值，并相应地更改具有对该值具有外键引用的任何表。

关于＃4，如果您将子ID更改为父表中不存在的内容（并且您具有引用完整性），则应该会收到外键错误。

- C-Pound Guru

---

我刚刚使用了 ON UPDATE CASCADE 来更新一个旧表中的主键，该表没有使用自增主键。 - BlueRaja - Danny Pflughoeft

当你从数据库中软删除用户（例如注销用户但保留其匿名化数据以进行历史统计），你可能还想匿名化其ID（例如重新生成一个），以便最小化再次找到其个人资料的方式（其ID可能在旧日志中被发现，以前的电子邮件中...）。 - Minishlink

Answer 2

1. 是的，这意味着例如如果您执行UPDATE parent SET id = 20 WHERE id = 10，所有父级ID为10的子项的parent_id也将更新为20。

2. 如果您不更新外键所引用的字段，则不需要此设置。

3. 想不到其他用途。

4. 由于外键约束会失败，因此您不能这样做。

- Zed

Answer 3

我认为你已经几乎掌握了要点！

如果您遵循数据库设计最佳实践，并且您的主键永远不会被更新（我认为这应该始终是这种情况），那么您实际上永远不需要使用ON UPDATE CASCADE子句。

Zed提出了一个好观点，如果您使用自然键（例如数据库表中的常规字段）作为主键，则可能存在某些情况需要更新主键。最近的一个例子是ISBN（国际标准书号），它不久前从10位数变成13位数字+字符。

如果您选择使用替代键（例如人工系统生成的键）作为主键（除非是极为罕见的情况，这将是我的首选），则情况就不同了。

所以最终结论是：如果您的主键永远不会更改，那么您永远不需要使用ON UPDATE CASCADE子句。

Marc

- marc_s

---

什么是“人工系统生成”的键？UUIDs？ - HPWD

@HPWD：只是一个“人工”键（一个不基于或派生自您实际数据的值），由系统生成 - 它可以是GUID、INT、BIGINT - 或任何其他东西 - 都无所谓。关键是：该值与您自己的实际数据无关 - 系统会自动为您生成该值。 - marc_s

@marc-s 感谢您抽出时间写下这篇回答。您的答案非常清晰易懂。 - HPWD

在我看来，一个只有自然键的单列表是枚举的一个好而干净的替代品（至少在MySQL DB flavors中）。例如，考虑一个带有行blue、purple、yellow的colors表，以及一个带有product_color列的products表，并被外键关联到colors表。这限制了像枚举一样的选择，但不像自增整数那样需要联接才能获取颜色名。在这种情况下，如果您决定将purple更名为violet，那么on update cascade是个好主意。 - okdewit

Answer 4

几天前我遇到了一个触发器问题，然后我发现ON UPDATE CASCADE很有用。看看这个例子（PostgreSQL）：

CREATE TABLE club
(
    key SERIAL PRIMARY KEY,
    name TEXT UNIQUE
);

CREATE TABLE band
(
    key SERIAL PRIMARY KEY,
    name TEXT UNIQUE
);

CREATE TABLE concert
(
    key SERIAL PRIMARY KEY,
    club_name TEXT REFERENCES club(name) ON UPDATE CASCADE,
    band_name TEXT REFERENCES band(name) ON UPDATE CASCADE,
    concert_date DATE
);

在我的问题中，我需要为更新音乐会表定义一些额外的操作（触发器）。这些操作必须修改俱乐部名称和乐队名称。由于引用关系，我无法完成这个任务。我不能先修改音乐会表，然后再处理俱乐部和乐队表。我也不能反过来做。ON UPDATE CASCADE是解决问题的关键。

- Ariel Grabijas

---

好的评论。我发现级联更新也很有用，无论如何都必须更改您的ID。我还同意其他人的看法，这种更改不应该太典型。例如，在您引用的情况下，我认为在大量数据中，可能使用文本字段关联外键不会导致数据库引擎的最快性能。请注意，如果音乐会表中的外部关系使用club.SERIAL和band.SERIAL，则名称的更改不会影响表之间的关系。但是，我认为ON UPDATE CASCADE是解决紧急情况的好工具。问候 - David L

这是一个值得怀疑的设计，使得它成为一个相当牵强的例子。如果您引用名称而不是每个表的主键，那么在club和band中保留两个SERIAL列作为主键的意义是什么？ - s.m.

简而言之，如果您需要从其他表复制字段并使其保持最新状态，则此功能非常有用。 - Kamil Tomšík

Answer 5

ON UPDATE和ON DELETE用于指定在父表中的行被更新和删除时执行的操作。以下是允许的操作：NO ACTION、CASCADE、SET NULL和SET DEFAULT。

父表中行的删除操作

如果您要删除父表中的一行或多行，可以设置以下操作之一：

• ON DELETE NO ACTION：SQL Server会引发一个错误，并回滚对父表中该行的删除操作。
• ON DELETE CASCADE：SQL Server会删除与从父表中删除的行对应的子表中的行。
• ON DELETE SET NULL：如果删除父表中相应的行，则SQL Server会将子表中的行设置为NULL。要执行此操作，外键列必须可为空。
• ON DELETE SET DEFAULT：如果删除父表中相应的行，则SQL Server会将子表中的行设置为它们的默认值。要执行此操作，外键列必须具有默认定义。请注意，如果未指定默认值，则可为空列的默认值为NULL。 默认情况下，如果您没有明确指定任何操作，SQL Server会应用ON DELETE NO ACTION。

父表中行的更新操作

如果您要更新父表中的一行或多行，可以设置以下操作之一：

• ON UPDATE NO ACTION：SQL Server会引发一个错误，并回滚对父表中该行的更新操作。
• ON UPDATE CASCADE：当父表中的行被更新时，SQL Server会更新子表中相应的行。
• ON UPDATE SET NULL：当父表中的对应行被更新时，SQL Server会将子表中的行设置为NULL。请注意，外键列必须是可为空的才能执行此操作。
• ON UPDATE SET DEFAULT：当父表中的对应行被更新时，SQL Server会为子表中具有相应行的行设置默认值。

FOREIGN KEY (foreign_key_columns)
    REFERENCES parent_table(parent_key_columns)
    ON UPDATE <action> 
    ON DELETE <action>;

请参阅参考教程。

- afruzan

Answer 6

这是一个很好的问题，昨天我也有同样的疑问。我思考了这个问题，特别搜索了一下是否存在像“ON UPDATE CASCADE”这样的东西，幸运的是SQL的设计者们也想到了这一点。我同意Ted.strauss的观点，并且我也评论了Noran的情况。

我什么时候使用它？就像Ted指出的那样，当您一次处理几个数据库，并且对其中一个表格进行修改时，在Ted所称的“卫星数据库”中具有任何形式的复制都无法保持原始ID，由于某种原因必须创建一个新的ID，例如由于权限或在您正在寻找快速解决方案的情况下（这种情况非常短暂，不值得完全遵守规范化的总规则，仅仅因为将是一个非常短暂的实用程序），无法更新旧数据。

所以，我在两个方面都同意：

(A.) 是的，在许多情况下，更好的设计可以避免它；但是

(B.) 在迁移、复制数据库或解决紧急情况的情况下，它是一个很棒的工具，幸运的是当我去搜索它是否存在时，它已经存在了。

- David L

Answer 7

我的评论主要参考第三点：如果我们假设父键不可更新，那么在什么情况下适用ON UPDATE CASCADE？这是一个例子。

我正在处理一个复制场景，其中多个卫星数据库需要与主数据库合并。每个卫星都在同一张表上生成数据，因此将表合并到主数据库会导致违反唯一性约束。我试图使用ON UPDATE CASCADE作为解决方案的一部分，在每次合并期间重新增加关键字。 ON UPDATE CASCADE应该通过自动化部分过程来简化此过程。

- ted.strauss

Answer 8

除了其他很好的答案之外，重要的是谨慎使用ON UPDATE CASCADE（或ON DELETE CASCADE...）。对具有此规范的表进行操作需要在底层关系上独占锁定。

如果您在一个表中有多个CASCADE定义（如其他答案中所述），尤其是多个使用相同定义的表和多个用户进行更新时，当一个进程在第一个底层表上获取独占锁定，另一个进程在第二个表上获取独占锁定时，这可能会创建死锁，并且它们通过无法同时获得两个（全部）独占锁来阻止彼此执行操作。

- Miro