They are called referential integrity constraints .

The relationships between different tables of a MySQL database that use the InnoDB storage engine can be specified in the form of foreign key constraints (“Foreign Key Constraints”), so that the database itself prevents operations from being carried out. which would cause inconsistencies.

The default behavior of a foreign key constraint is to prevent a change to the database as a result of a statement DELETEor UPDATE, if this would result in a referential integrity failure.

We will first see in summary the different referential integrity constraints, making use of some images for the case of ON DELETE.

The images consider two tables personasand ciudadesrelated by the column ciudad_id:

_{Image by A. Cedano for stackoverflow.es}

RESTRICT

• RESTRICT: This is the default behavior, which prevents modifications that violate referential integrity.

  In the image we see the result of this query:

  DELETE FROM ciudades WHERE ciudad_id=4;
  

  We see that the record can be deleted because there is no related record in the table personas.

_{Image by A. Cedano for stackoverflow.es}

Instead this query:

    DELETE FROM ciudades WHERE ciudad_id=1;

It would throw an error message:

Cannot delete or update a parent row: a foreign key constraint fails ( db. personas, CONSTRAINT personas_ibfk_1FOREIGN KEY ( ciudad_id) REFERENCES ciudades( ciudad_id))

Because DELETEhe violates the restriction. If the row 1of ciudadeswere deleted, the rows 1and 4of personaswould be orphaned , that is, without relation in the table ciudades.

CASCADE

• CASCADE: Deletes the records of the dependent table when the record of the parent table is deleted (in a statement DELETE), or updates the value of the child key when the value of the referenced key is updated (in a statement UPDATE).

  In the image we see the result of this query:

  DELETE FROM ciudades WHERE ciudad_id=1;
  

  CASCADE Here, all records personasthat have ciudad_idequal to will be deleted in cascade 1, and as is evident, it will be deleted in ciudadesthe city with id 1.

_{Image by A. Cedano for stackoverflow.es}

SET NULL

• SET NULL: Sets NULLthe value of the secondary key when the record in the main table is deleted or the value of the referenced field is modified.

  What we see in the image is the result of this query:

  DELETE FROM ciudades WHERE ciudad_id=1;
  

  ciudad_idHere the table column personaswill set the values ​​to NULL, in all rows whose ciudad_idis equal to 1. And as is evident, it will be deleted in ciudadesthe city with id 1.

_{Image by A. Cedano for stackoverflow.es}

For what happens in this case it is important to note that this CREATE TABLE:

CREATE TABLE IF NOT EXISTS personas 
(
    persona_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    persona_nom VARCHAR(70),
    ciudad_id INT NOT NULL,
    FOREIGN KEY fk_ciudad(ciudad_id) REFERENCES ciudades(ciudad_id)
    ON DELETE SET NULL

)ENGINE=INNODB;

It will throw the error:

Cannot add foreign key constraint

I think it's not hard to figure out why :)

NO ACTION

• NO ACTION: In MySQL it works the same as RESTRICT. See explanation below.

test code

I leave here a demo so that we can test with real data how each restriction works. We will only have to change the configuration of the restriction in CREATE TABLEputting the different possibilities.

The example is based on DELETE, but we can write and test queries UPDATEif we wish.

This is the link.

VER DEMO

More about restrictions

What follows is almost all taken from the MySQL documentation.

The constraints, both primary-foreign keys, are generally indicated when creating the table ( CREATE TABLE). Those related to referential integrity must be indicated explicitly, since MySQL does not deduce them based on the indicated primary-foreign keys. If they are not created during CREATE TABLEthey can be modified later using ALTER TABLE. The documentation explains how to do it.

There are two types of constraints: ON DELETEand ON UPDATE. And within them in turn several possibilities (it is the same for both).

Let's explain it with the documentation :

For storage engines that support foreign keys, MySQL rejects any operation INSERTor UPDATEattempts to create a foreign key value on a child table if there is no matching candidate key value in the parent table.

When an UPDATEor operation DELETEaffects a key value in the parent table that has matching rows in the child table, the result depends on the referential action specified using the ON UPDATEand ON DELETEclause subclauses FOREIGN KEY. MySQL supports five options regarding the action to take:

• CASCADE: Delete or update the parent table row and automatically delete or update matching rows in the child table. As long ON DELETE CASCADEas ON UPDATE CASCADEthey are compatible. ON UPDATE CASCADEBetween two tables, multiple clauses that act on the same column in either the parent table or the child table are not defined .

Note Cascading foreign key actions do not fire triggers.

• SET NULL: Delete or update the parent table row and set the foreign key column(s) in the child table to NULL. ON DELETE SET NULLThe and clauses are supported ON UPDATE SET NULL.

If you specify an action SET NULL, make sure you haven't declared the child table's columns as NOT NULL.

• RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT(or ) is the same as omitting the or NO ACTIONclause .ON DELETEON UPDATE

• NO ACTION: A standard SQL keyword. In MySQL, equivalent to RESTRICT. The MySQL server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and NO ACTIONit is a deferred check. In MySQL, foreign key constraints are checked immediately, so it NO ACTIONis the same as RESTRICT.

• SET DEFAULT: This action is recognized by the MySQL parser, but both InnoDB and NDB reject table definitions that contain ON DELETE SET DEFAULTor clauses ON UPDATE SET DEFAULT.

For one ON DELETEor ON UPDATEthat is not specified, the default action is always RESTRICT.

MySQL supports foreign key references between one column and another within a table. (A column cannot have a foreign key reference to itself.) In these cases, "child table records" actually refers to dependent records within the same table.

A foreign key constraint on a stored generated column cannot use ON UPDATE CASCADE, ON DELETE SET NULL, ON UPDATE SET NULL, ON DELETE SET DEFAULTor ON UPDATE SET DEFAULT.

A foreign key constraint cannot reference a virtual generated column.

For InnoDB restrictions related to foreign keys and generated columns, see Section 14.8.1.6, "InnoDB and FOREIGN KEY Restrictions" .

Referential actions indicate how an operation UPDATEor DELETEon the parent table will affect related rows in the child table. You can find the differences between the actions in the MySQL documentation :

• CASCADE: When you do UPDATE/ DELETEon the parent table, it will automatically update/delete the related rows in the child table.
• SET NULL: When doing UPDATE/ DELETEon the parent table, the foreign keys of related rows in the child table will be set to NULL. It is important to make sure that those fields will not be restricted from NOT NULLor you may receive errors.
• RESTRICT: if you try to do an action of UPDATE/ DELETEon the parent table it will be automatically rejected.
• NO ACTION: This is an SQL keyword, in MySQL it is equivalent to RESTRICT.

There is a fifth value SET DEFAULTthat, although accepted by the MySQL processor, can cause problems with InnoDB and NDB which will reject tables defined with that value.

In MySQL, only the InnoDB engine currently supports foreign key constraints. The syntax for foreign key constraints in InnoDB is as follows:

ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_NAME] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

CASCADE:

When updating/deleting records in parent table, update/delete matching records in child table at the same time

SET NULL：

When updating/deleting records in the parent table, set the matching record's column in the child table to null [note that the corresponding foreign key column in the child table cannot be set to NOT NULL]

NO ACTION:

Refuse to update or drop or delete parent table

RESTRICT :

Refuse to update or delete the parent table

Note: Specifying RESTRICT or NOACTION has the same effect as ignoring ON DELETE or ON UPDATE.