何时使用“ON UPDATE CASCADE" [英] When to use "ON UPDATE CASCADE"

问题描述

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

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.

为了便于讨论，让我们看一些代码.

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

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

For ON DELETE CASCADE, if a parent with an id is deleted, a record in child with parent_id = parent.id will be automatically deleted. This should be no problem.

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

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

如果 (1) 为真，则表示如果 parent.id 不可更新(或永远不会更新)，则无需使用 ON UPDATE CASCADE更新)就像它是 AUTO_INCREMENT 或始终设置为 TIMESTAMP 一样.对吗?

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

如果(2)不成立，在什么情况下我们应该使用ON UPDATE CASCADE?

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

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

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

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

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

请多指教.

推荐答案

确实，如果您的主键只是一个自动递增的标识值，那么 ON UPDATE CASCADE 将没有真正的用途.

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

但是，假设您的主键是 10 位 UPC 条形码，由于扩展，您需要将其更改为 13 位 UPC 条形码.在这种情况下，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.

参考 #4，如果您将子 ID 更改为父表中不存在的东西(并且您具有参照完整性)，您应该会收到外键错误.

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.

这篇关于何时使用“ON UPDATE CASCADE"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！