How to use ON UPDATE and ON DELETE

The following are 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;

The above example code is copied from MySQL 8.0 Reference Manual

Referential Actions

When an “UPDATE or DELETE” 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: By updating an id in the table ‘parent’, the engine will reject the operation if one ‘child’ at least links to this ‘parent’. This is the default value.

ON UPDATE NO ACTION: same as RESTRICT.

ON UPDATE CASCADE: By 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 the ‘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 the related parent_id of the table ‘child’ to NULL.

ON DELETE clause:

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

ON DELETE NO ACTION: same as RESTRICT.

ON DELETE CASCADE: By deleting a ‘parent’ row in the table ‘parent’, the engine will also delete the related ‘child’.

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