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

问题描述

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

pre $ $ codeREATE TABLE parent
id INT NOT NULL AUTO_INCREMENT，
PRIMARY KEY（id）
）;

CREATE TABLE子元素（
id INT NOT NULL AUTO_INCREMENT，parent_id INT，
INDEX par_ind（parent_id），
FOREIGN KEY（parent_id）
REFERENCES父级（id）
ON DELETE CASCADE
）;

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

1. 这意味着ON UPDATE CASCADE会在

ON UPDATE CASCADE如果 parent.id 不可更新（或永远不会更新），就像是 AUTO_INCREMENT 或始终设置为 TIMESTAMP 。如果（2）不正确，我们应该在什么情况下使用ON UPDATE CASCADE？
/ li>


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

   那么，我知道，上面的一些问题可以通过编程测试来理解，但我也想知道
   
   

   请说明一下。
   

   解决方案<如果你的主键只是一个自动递增的标识值，那么你就没有真正的使用ON UPDATE CASCADE了。

   
   
   

   然而，让我们假设你的主键是一个10位的UPC条形码，并且由于扩展，你需要把它改成一个13位的UPC条形码。在这种情况下，ON UPDATE CASCADE将允许您更改主键值，并且任何具有外键引用的表的值都将相应地更改。

   
   
   

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

   
   

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

   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. This means that "ON UPDATE CASCADE" will do the same thing when id of the parent is updated?

   2. 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?

   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_id to be something not existing, will it then be automatically deleted?

   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.

   解决方案

   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.

   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.

   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屋！