When to use "ON UPDATE CASCADE"

Question

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.

Answer 1

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.