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 foundabout 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
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.