How to use ON UPDATE and ON DELETE

The following is the notes about MySQL FOREIGN KEY Constraints.

MySQL supports foreign keys, which permit cross-referencing related data across tables, and foreign key constraints, which help keep the associated data consistent.

MySQL, however, does not have advanced constraints like PostgreSQL, though the foreign key constraints are pretty advanced.

I’ve found a comprehensible explanation about how to put constraints on the MySQL database.

The following example shows the Foreign Key Constraint relates to parent and child tables through a single-column foreign key:

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

Above example code is copied from MySQL 8.0 Reference Manual

Referential Actions

When an UPDATE or DELETE operation affects a key value in the parent table that has matching rows in the child table, the result depends on the referential action specified by ON UPDATE and ON DELETE subclauses of the FOREIGN KEY clause.

ON UPDATE clause:

ON UPDATE RESTRICT: updating an id in table ‘parent’ the engine will reject the operation if one ‘child’ at least links on this ‘parent’. This is the default value.

ON UPDATE NO ACTION: same as RESTRICT.

ON UPDATE CASCADE: updating an id in a row of table ‘parent’ the engine will update it accordingly on all ‘child’ rows referencing this ‘parent’ (but no triggers activated on ‘child’ table, though the engine will track the changes).

ON UPDATE SET NULL: updating an id in a row of table ‘parent’ the engine will set related parent_id of the table ‘child’ to NULL.

ON DELETE clause:

ON DELETE RESTRICT: trying to delete an id in table ‘parent’ the engine will reject the operation if one ‘child’ at least links on this ‘parent’. This is the default value.

ON DELETE NO ACTION: same as RESTRICT.

ON DELETE CASCADE: deleting a ‘parent’ row in table ‘parent’ the engine will delete as well the related ‘child’.

ON DELETE SET NULL: deleting a ‘parent’ row the related ‘child’ will automatically have the relationship to NULL.