When to use "ON UPDATE CASCADE"

1. Yes, it means that for example if you do UPDATE parent SET id = 20 WHERE id = 10 all children parent_id's of 10 will also be updated to 20

2. If you don't update the field the foreign key refers to, this setting is not needed

3. Can't think of any other use.

4. You can't do that as the foreign key constraint would fail.

I think you've pretty much nailed the points!

If you follow database design best practices and your primary key is never updatable (which I think should always be the case anyway), then you never really need the ON UPDATE CASCADE clause.

Zed made a good point, that if you use a natural key (e.g. a regular field from your database table) as your primary key, then there might be certain situations where you need to update your primary keys. Another recent example would be the ISBN (International Standard Book Numbers) which changed from 10 to 13 digits+characters not too long ago.

This is not the case if you choose to use surrogate (e.g. artifically system-generated) keys as your primary key (which would be my preferred choice in all but the most rare occasions).

So in the end: if your primary key never changes, then you never need the ON UPDATE CASCADE clause.

Marc

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.

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.

A few days ago I've had an issue with triggers, and I've figured out that ON UPDATE CASCADE can be useful. Take a look at this example (PostgreSQL):

CREATE TABLE club
(
    key SERIAL PRIMARY KEY,
    name TEXT UNIQUE
);

CREATE TABLE band
(
    key SERIAL PRIMARY KEY,
    name TEXT UNIQUE
);

CREATE TABLE concert
(
    key SERIAL PRIMARY KEY,
    club_name TEXT REFERENCES club(name) ON UPDATE CASCADE,
    band_name TEXT REFERENCES band(name) ON UPDATE CASCADE,
    concert_date DATE
);

In my issue, I had to define some additional operations (trigger) for updating the concert's table. Those operations had to modify club_name and band_name. I was unable to do it, because of reference. I couldn't modify concert and then deal with club and band tables. I couldn't also do it the other way. ON UPDATE CASCADE was the key to solve the problem.