When to use ON UPDATE CASCADE

Question

I frequently use ON DELETE CASCADE, but I never use ON UPDATE CASCADE because I'm not sure when it will be useful.

Let's look at some code for the discussion's sake.

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

If a parent with an id is deleted for ON DELETE CASCADE, a record in the child with parent id = parent.id will also be erased automatically. There should be no issue with this.

1. This implies that ON UPDATE CASCADE will act in the same way when the parent's ID is modified, right?
2. If (1) is correct, there is no need to use ON UPDATE CASCADE if parent.id cannot be updated or will never be modified, such as when it is set to AUTO INCREMENT or is always set to TIMESTAMP. Is that accurate?
3. What other circumstance should we utilize ON UPDATE CASCADE in if (2) is false?
4. What happens if I update the child.parent id to something that doesn't already exist? Will it then be destroyed by default?

I am aware that some of the aforementioned questions can be tested programmatically for understanding, but I also want to know if any of this is dependant on the database vendor or not. Can someone please help me solve this?

Answer 1

It's true that ON UPDATE CASCADE wouldn't really be useful if your primary key was merely an identity value that was automatically increased.

However, suppose your primary key is a 10-digit UPC bar code and you need to alter it to a 13-digit UPC bar code because of expansion. If so, you could modify the primary key value using ON UPDATE CASCADE, and all tables that used the value as a foreign key reference would be updated correspondingly.

Regarding #4, you should get a foreign key error if you modify the child ID to a value that doesn't exist in the parent database (and you have referential integrity).

I hope this helps you.