When to use "ON UPDATE CASCADE"

user_176180 0
Viewed 387767

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

Please shed some light.

foreign-keys sql
8 Answers

The ON UPDATE and ON DELETE specify which action will execute when a row in the parent table is updated and deleted. The following are permitted actions : NO ACTION, CASCADE, SET NULL, and SET DEFAULT.

Delete actions of rows in the parent table

If you delete one or more rows in the parent table, you can set one of the following actions:

  • ON DELETE NO ACTION: SQL Server raises an error and rolls back the delete action on the row in the parent table.
  • ON DELETE CASCADE: SQL Server deletes the rows in the child table that is corresponding to the row deleted from the parent table.
  • ON DELETE SET NULL: SQL Server sets the rows in the child table to NULL if the corresponding rows in the parent table are deleted. To execute this action, the foreign key columns must be nullable.
  • ON DELETE SET DEFAULT: SQL Server sets the rows in the child table to their default values if the corresponding rows in the parent table are deleted. To execute this action, the foreign key columns must have default definitions. Note that a nullable column has a default value of NULL if no default value specified. By default, SQL Server appliesON DELETE NO ACTION if you don’t explicitly specify any action.

Update action of rows in the parent table

If you update one or more rows in the parent table, you can set one of the following actions:

  • ON UPDATE NO ACTION: SQL Server raises an error and rolls back the update action on the row in the parent table.
  • ON UPDATE CASCADE: SQL Server updates the corresponding rows in the child table when the rows in the parent table are updated.
  • ON UPDATE SET NULL: SQL Server sets the rows in the child table to NULL when the corresponding row in the parent table is updated. Note that the foreign key columns must be nullable for this action to execute.
  • ON UPDATE SET DEFAULT: SQL Server sets the default values for the rows in the child table that have the corresponding rows in the parent table updated.
FOREIGN KEY (foreign_key_columns)
    REFERENCES parent_table(parent_key_columns)
    ON UPDATE <action> 
    ON DELETE <action>;

See the reference tutorial.

user_49832010

To add to other great answers here it is important to use ON UPDATE CASCADE (or on DELETE CASCADE...) cautiously. Operations on tables with this specification require exclusive lock on underlaying relations.

If you have multiple CASCADE definitions in one table (as in other answer), and especially multiple tables using same definitions, and multiple users updating, this can create a deadlock when one process acquires exclusive lock on first underlaying table, other exclusive lock on second, and they block out each other by none of them being able to get both (all) exclusive locks to perform operation.

user_30438370
Related