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 a child with parent_id = parent.id will be automatically deleted. This should be no problem.

Does this mean that "ON UPDATE CASCADE" will do the same thing when id of the parent is updated?

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?

If (2) is not true, in what other kinds of situations should we use "ON UPDATE CASCADE"?

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 questions above can be tested programmatically to understand but I want also to know if any of this is database vendor dependent or not.

Please shed some light.

Answer 1

• Primary Key is the identity value which is auto-incremented, you can’t have a real use for ON UPDATE CASCADE. 
• Consider that your primary key is a 10-digit UPC bar code and because of the expansion, you may need to change it to a 13-digit UPC bar code. 
• In such a case, ON UPDATE CASCADE will allow you to change the primary key value and if any table has the foreign key references to the value then it will be changed accordingly.
• If you change the child ID to something else that doesn't exist in the parent table (and you have referential integrity), then you’ll get the foreign key error.