SQL 何时使用“ON UPDATE CASCADE”

When to use "ON UPDATE CASCADE"

提问by NawaMan

I use "ON DELETE CASCADE" regularly but I never use "ON UPDATE CASCADE" as I am not so sure in what situation it will be useful.

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

For the sake of discussion let see some code.

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

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

For "ON DELETE CASCADE", if a parent with an idis deleted, a record in child with parent_id = parent.idwill be automatically deleted. This should be no problem.

对于“ON DELETE CASCADE”，如果id删除了带有 的父级，parent_id = parent.id则将自动删除带有 的子级中的记录。这应该没有问题。

1. This means that "ON UPDATE CASCADE" will do the same thing when idof the parent is updated?

2. If (1) is true, it means that there is no need to use "ON UPDATE CASCADE" if parent.idis not updatable (or will never be updated) like when it is AUTO_INCREMENTor always set to be TIMESTAMP. Is that right?

3. If (2) is not true, in what other kind of situation should we use "ON UPDATE CASCADE"?

4. What if I (for some reason) update the child.parent_idto be something not existing, will it then be automatically deleted?

1. 这意味着“ON UPDATE CASCADE”在id更新父级时会做同样的事情吗？

2. 如果 (1) 为真，则意味着如果parent.id不可更新（或永远不会更新），则无需使用“ON UPDATE CASCADE”，就像它是AUTO_INCREMENT或始终设置为 一样TIMESTAMP。那正确吗？

3. 如果（2）不正确，我们应该在什么其他情况下使用“ON UPDATE CASCADE”？

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

Well, I know, some of the question above can be test programmically to understand but I want also know if any of this is database vendor dependent or not.

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

Please shed some light.

请说明一下。

回答by C-Pound Guru

It's true that if your primary key is just a identity value auto incremented, you would have no real use for ON UPDATE CASCADE.

确实，如果您的主键只是一个自动递增的身份值，那么您将没有真正使用 ON UPDATE CASCADE。

However, let's say that your primary key is a 10 digit UPC bar code and because of expansion, you need to change it to a 13-digit UPC bar code. In that case, ON UPDATE CASCADE would allow you to change the primary key value and any tables that have foreign key references to the value will be changed accordingly.

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

In reference to #4, if you change the child ID to something that doesn't exist in the parent table (and you have referential integrity), you should get a foreign key error.

参考 #4，如果您将子 ID 更改为父表中不存在的内容（并且您具有参照完整性），您应该会收到外键错误。

回答by Zed

1. Yes, it means that for example if you do UPDATE parent SET id = 20 WHERE id = 10all children parent_id's of 10 will also be updated to 20

2. If you don't update the field the foreign key refers to, this setting is not needed

3. Can't think of any other use.

4. You can't do that as the foreign key constraint would fail.

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

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

3. 想不出其他用途。

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

回答by marc_s

I think you've pretty much nailed the points!

我认为你已经非常确定要点了！

If you follow database design best practices and your primary key is never updatable (which I think should always be the case anyway), then you never really need the ON UPDATE CASCADEclause.

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

Zedmade a good point, that if you use a naturalkey (e.g. a regular field from your database table) as your primary key, then there might be certain situations where you need to update your primary keys. Another recent example would be the ISBN (International Standard Book Numbers) which changed from 10 to 13 digits+characters not too long ago.

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

This is not the case if you choose to use surrogate(e.g. artifically system-generated) keys as your primary key (which would be my preferred choice in all but the most rare occasions).

如果您选择使用代理（例如，人为系统生成的）键作为主键（这将是我的首选，但在极少数情况下），情况并非如此。

So in the end: if your primary key never changes, then you never need the ON UPDATE CASCADEclause.

所以最后：如果你的主键永远不会改变，那么你永远不需要这个ON UPDATE CASCADE子句。

Marc

马克

回答by Ariel Grabijas

A few days ago I've had an issue with triggers, and I've figured out that ON UPDATE CASCADEcan be useful. Take a look at this example (PostgreSQL):

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

In my issue, I had to define some additional operations (trigger) for updating the concert's table. Those operations had to modify club_name and band_name. I was unable to do it, because of reference. I couldn't modify concert and then deal with club and band tables. I couldn't also do it the other way. ON UPDATE CASCADEwas the key to solve the problem.

在我的问题中，我必须定义一些额外的操作（触发器）来更新 Concert 的表。这些操作必须修改 club_name 和 band_name。由于参考，我无法做到。我无法修改音乐会，然后处理俱乐部和乐队表。我也不能以其他方式做到这一点。ON UPDATE CASCADE是解决问题的关键。

回答by ted.strauss

My comment is mainly in reference to point #3: under what circumstances is ON UPDATE CASCADE applicable if we're assuming that the parent key is not updateable? Here is one case.

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

I am dealing with a replication scenario in which multiple satellite databases need to be merged with a master. Each satellite is generating data on the same tables, so merging of the tables to the master leads to violations of the uniqueness constraint. I'm trying to use ON UPDATE CASCADE as part of a solution in which I re-increment the keys during each merge. ON UPDATE CASCADE should simplify this process by automating part of the process.

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

回答by David L

It's an excellent question, I had the same question yesterday. I thought about this problem, specifically SEARCHED if existed something like "ON UPDATE CASCADE" and fortunately the designers of SQL had also thought about that. I agree with Ted.strauss, and I also commented Noran's case.

这是一个很好的问题，我昨天也有同样的问题。我想过这个问题，特别是 SEARCHED 如果存在诸如“ON UPDATE CASCADE”之类的东西，幸运的是 SQL 的设计者也考虑过这个问题。我同意 Ted.strauss 的观点，我也评论了诺兰的案例。

When did I use it? Like Ted pointed out, when you are treating several databases at one time, and the modification in one of them, in one table, has any kind of reproduction in what Ted calls "satellite database", can't be kept with the very original ID, and for any reason you have to create a new one, in case you can't update the data on the old one (for example due to permissions, or in case you are searching for fastness in a case that is so ephemeral that doesn't deserve the absolute and utter respect for the total rules of normalization, simply because will be a very short-lived utility)

我什么时候用过？就像 Ted 指出的那样，当您一次处理多个数据库时，对其中一个数据库的修改，在一张表中，在 Ted 所谓的“卫星数据库”中具有任何形式的复制，不能与原始数据库保持一致ID，并且出于任何原因，您必须创建一个新的，以防您无法更新旧的数据（例如，由于权限，或者如果您在如此短暂的情况下搜索 fastness，不值得绝对和完全尊重规范化的总规则，仅仅因为它将是一个非常短暂的实用程序）

So, I agree in two points:

所以，我同意两点：

(A.) Yes, in many times a better design can avoid it; BUT

(A.) 是的，很多时候更好的设计可以避免它；但

(B.) In cases of migrations, replicating databases, or solving emergencies, it's a GREAT TOOL that fortunately was there when I went to search if it existed.

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