When to Use "On Update Cascade"

When to use ON UPDATE CASCADE

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.

why i have an error when i use on update cascade?

there is no ON UPDATE CASCADE in oracle.

you may want to look at deferrable constraints which defers the foreign key check until commit, update the parent, update the child and then commit.

ALTER TABLE affaire_cassation
ADD CONSTRAINT key_fk_num_p 
FOREIGN KEY(num_aff_a) 
REFERENCES affaire_appel(num_aff)
DEFERRABLE INITIALLY DEFERRED;

You may also look for DEFERRABLE INITIALLY IMMEDIATE where you can defer the constraints on demand when you need it.

use with ALTER SESSION SET CONSTRAINTS = DEFERRED;

Foreign key constraints: When to use ON UPDATE and ON DELETE

Do not hesitate to put constraints on the database. You'll be sure to have a consistent database, and that's one of the good reasons to use a database. Especially if you have several applications requesting it (or just one application but with a direct mode and a batch mode using different sources).

With MySQL you do not have advanced constraints like you would have in postgreSQL but at least the foreign key constraints are quite advanced.

We'll take an example, a company table with a user table containing people from theses company

CREATE TABLE COMPANY (
     company_id INT NOT NULL,
     company_name VARCHAR(50),
     PRIMARY KEY (company_id)
) ENGINE=INNODB;

CREATE TABLE USER (
     user_id INT, 
     user_name VARCHAR(50), 
     company_id INT,
     INDEX company_id_idx (company_id),
     FOREIGN KEY (company_id) REFERENCES COMPANY (company_id) ON...
) ENGINE=INNODB;

Let's look at the ON UPDATE clause:

ON UPDATE RESTRICT : the default : if you try to update a company_id in table COMPANY the engine will reject the operation if one USER at least links on this company.
ON UPDATE NO ACTION : same as RESTRICT.
ON UPDATE CASCADE : the best one usually : if you update a company_id in a row of table COMPANY the engine will update it accordingly on all USER rows referencing this COMPANY (but no triggers activated on USER table, warning). The engine will track the changes for you, it's good.
ON UPDATE SET NULL : if you update a company_id in a row of table COMPANY the engine will set related USERs company_id to NULL (should be available in USER company_id field). I cannot see any interesting thing to do with that on an update, but I may be wrong.

And now on the ON DELETE side:

ON DELETE RESTRICT : the default : if you try to delete a company_id Id in table COMPANY the engine will reject the operation if one USER at least links on this company, can save your life.
ON DELETE NO ACTION : same as RESTRICT
ON DELETE CASCADE : dangerous : if you delete a company row in table COMPANY the engine will delete as well the related USERs. This is dangerous but can be used to make automatic cleanups on secondary tables (so it can be something you want, but quite certainly not for a COMPANY<->USER example)
ON DELETE SET NULL : handful : if you delete a COMPANY row the related USERs will automatically have the relationship to NULL. If Null is your value for users with no company this can be a good behavior, for example maybe you need to keep the users in your application, as authors of some content, but removing the company is not a problem for you.

usually my default is: ON DELETE RESTRICT ON UPDATE CASCADE. with some ON DELETE CASCADE for track tables (logs--not all logs--, things like that) and ON DELETE SET NULL when the master table is a 'simple attribute' for the table containing the foreign key, like a JOB table for the USER table.

Edit

It's been a long time since I wrote that. Now I think I should add one important warning. MySQL has one big documented limitation with cascades. Cascades are not firing triggers. So if you were over confident enough in that engine to use triggers you should avoid cascades constraints.

http://dev.mysql.com/doc/refman/5.6/en/triggers.html

MySQL triggers activate only for changes made to tables by SQL statements. They do not activate for changes in views, nor by changes to tables made by APIs that do not transmit SQL statements to the MySQL Server

http://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html#stored-routines-trigger-restrictions

==> See below the last edit, things are moving on this domain

Triggers are not activated by foreign key actions.

And I do not think this will get fixed one day. Foreign key constraints are managed by the InnoDb storage and Triggers are managed by the MySQL SQL engine. Both are separated. Innodb is the only storage with constraint management, maybe they'll add triggers directly in the storage engine one day, maybe not.

But I have my own opinion on which element you should choose between the poor trigger implementation and the very useful foreign keys constraints support. And once you'll get used to database consistency you'll love PostgreSQL.

12/2017-Updating this Edit about MySQL:

as stated by @IstiaqueAhmed in the comments, the situation has changed on this subject. So follow the link and check the real up-to-date situation (which may change again in the future).

Difference between On Delete Cascade & On Update Cascade in mysql

on delete cascade

It will delete all the child records when parent record is deleted, so
that there will be no child record when parent is deleted.

on update cascade

It will update the child records parent_id when parent record id that
change. In rare case we use on update cascade

eg:- suppose your parent id is 4 digit and due to expansion later on
you need change it to 10 digit. 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.

SQL Learner -- MS Access 2013 ON UPDATE CASCADE and ON DELETE CASCADE

The ON UPDATE CASCADE and ON DELETE CASCADE statements are not supported by Access (see https://msdn.microsoft.com/en-us/library/office/ff836971.aspx).

Regarding the functionality, you should not have a need to use ON UPDATE CASCADE. This constraint means that if you do change a primary key in the master table, the changes propagate to every child table referencing the master table. Changing a primary key is considered a no-go in the SQL world. Is you really must change primary keys (because of some kind of disaster struck), this will be done with a script, lots of backup and extreme care. A primary key is this: a unique (ideally globally), immutable identifier of a row in a table.

The ON DELETE CASCADE means that if you remove a row in the master table, any rows referencing this key in child tables will also be deleted. While this sounds like a lazy shortcut, I would recommend against it and doing this within application logic (there might be a use case where you want to retain records or log them or do something with them instead of blindly erasing them from the database.

When/Why to use Cascading in SQL Server?

Summary of what I've seen so far:

Some people don't like cascading at all.

Cascade Delete

Cascade Delete may make sense when the semantics of the relationship can involve an exclusive "is part of" description. For example, an OrderLine record is part of its parent order, and OrderLines will never be shared between multiple orders. If the Order were to vanish, the OrderLine should as well, and a line without an Order would be a problem.
The canonical example for Cascade Delete is SomeObject and SomeObjectItems, where it doesn't make any sense for an items record to ever exist without a corresponding main record.
You should not use Cascade Delete if you are preserving history or using a "soft/logical delete" where you only set a deleted bit column to 1/true.

Cascade Update

Cascade Update may make sense when you use a real key rather than a surrogate key (identity/autoincrement column) across tables.
The canonical example for Cascade Update is when you have a mutable foreign key, like a username that can be changed.
You should not use Cascade Update with keys that are Identity/autoincrement columns.
Cascade Update is best used in conjunction with a unique constraint.

When To Use Cascading

You may want to get an extra strong confirmation back from the user before allowing an operation to cascade, but it depends on your application.
Cascading can get you into trouble if you set up your foreign keys wrong. But you should be okay if you do that right.
It's not wise to use cascading before you understand it thoroughly. However, it is a useful feature and therefore worth taking the time to understand.

MariaDB ON UPDATE CASCADE CONSTRAINT not working as expected?

See the comment on this page:

https://mariadb.com/kb/en/foreign-keys/

If ON UPDATE CASCADE recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT. This means that you cannot use self-referential ON UPDATE CASCADE operations. This is to prevent infinite loops resulting from cascaded updates.

In other words, ON UPDATE CASCADE does not work if it's a hierarchical-data kind of table.

When to Use "On Update Cascade"