何时使用“ON UPDATE cascade”;

我经常使用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不可更新(或永远不会更新)，就不需要使用ON UPDATE CASCADE，比如当它是AUTO_INCREMENT或总是设置为TIMESTAMP时。对吗?

  3. 如果(2)不为真，在其他哪种情况下我们应该使用ON UPDATE CASCADE?

  4. 如果我(出于某种原因)将child.parent_id更新为不存在的东西，它会被自动删除吗?

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

请照点光。

397864 次浏览
小开
最佳答案

的确，如果你的主键只是一个自动递增的标识值，你就不会真正使用ON UPDATE CASCADE。

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

参考#4，如果您将子ID更改为父表中不存在的内容(并且具有引用完整性)，则应该会得到外键错误。

小开

  1. 是的，这意味着例如，如果你执行UPDATE parent SET id = 20 WHERE id = 10，所有parent_id为10的子节点也将被更新为20

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

  3. 我想不出还有什么别的用途。

  4. 你不能这样做，因为外键约束会失效。

小开

我认为你已经抓住了要点!

如果你遵循数据库设计最佳实践，并且你的主键永远不可更新(我认为无论如何都应该是这样)，那么你永远不需要ON UPDATE CASCADE子句。

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

如果你选择使用代理(例如，人工系统生成的)键作为你的主键(在大多数情况下，这将是我的首选)，情况就不是这样了。

所以最后:如果你的主键从未改变，那么你永远不需要ON UPDATE CASCADE子句。

马克

小开

我的评论主要是参考第3点:在什么情况下是ON UPDATE CASCADE适用，如果我们假设父键是不可更新的?这里有一种情况。

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

小开

几天前，我遇到了一个触发器问题，我发现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
);

在我的问题中，我必须定义一些额外的操作(触发器)来更新音乐会的表。这些操作需要修改“club_name”和“band_name”。因为参考问题，我没能做这件事。我不能修改音乐会，然后处理俱乐部和乐队的桌子。我不能用另一种方法来做。ON UPDATE CASCADE是解决问题的关键。

小开

这是一个很好的问题，我昨天也有同样的问题。我考虑过这个问题，特别是搜索是否存在类似“ON UPDATE CASCADE”的东西，幸运的是SQL的设计师也考虑过这个问题。我同意泰德的观点。施特劳斯，我还评论了诺兰的案子。

我什么时候用过?就像Ted指出的，当你同时处理几个数据库时，其中一个数据库的修改，在一个表中，在Ted所谓的“卫星数据库”中有任何形式的复制，不能用原始的ID保存，由于任何原因，你必须创建一个新的ID，以防你不能更新旧数据库上的数据(例如由于权限，或者，如果你在一个如此短暂的情况下寻找快速，不值得对归一化的全部规则的绝对和彻底的尊重，因为这将是一个非常短暂的效用)

因此，我同意两点:

(a)是的，在很多时候，一个更好的设计可以避免这种情况;但

(b)在迁移、复制数据库或解决紧急情况的情况下，它是一个很好的工具，幸运的是，当我去搜索它是否存在时，它就在那里。

小开

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将应用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>;

看到参考教程。

小开

为了补充其他精彩的答案，在这里谨慎使用ON UPDATE CASCADE(或ON DELETE CASCADE…)是很重要的。对具有此规范的表的操作需要对底层关系进行排他锁定。

如果在一个表中有多个CASCADE定义(如其他的答案)，特别是多个表使用相同的定义，并且多个用户更新，当一个进程在第一个底层表上获得排他锁，在第二个底层表上获得其他排他锁，并且它们通过没有一个进程能够获得两个(所有)排他锁来执行操作而相互阻塞时，就会产生死锁。


提交答案