What is the purpose of the RESTRICT, CASCADE, SET NULL and NO ACTION options?

Question

When creating a foreign key type relationship between two tables in Mysql, I can specify some additional options in the events ON UPDATE and ON DELETE that are associated with alteration and deletion of records.

The options are:

1. RESTRICT
2. CASCADE
3. SET NULL
4. NO ACTION

A practical illustration example using the option NO ACTION, see below:

CREATE TABLE `usuariorelsupermercado` (
    `idUsuario` INT(11) NOT NULL,
    `idSupermercado` INT(11) NOT NULL,
    INDEX `fk_usuario_rel` (`idUsuario`),
    INDEX `fk_supermercado_rel` (`idSupermercado`),
    CONSTRAINT `fk_supermercado_rel` FOREIGN KEY (`idSupermercado`) REFERENCES `supermercado` (`idSupermercado`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `fk_usuario_rel` FOREIGN KEY (`idUsuario`) REFERENCES `usuario` (`idUsuario`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

I have some doubts about these options.

Doubts

• What is the purpose of each of these options?
• What these options influence in my database?

Answer 1

These are options for foreign keys, trying to simplify to the maximum:

RESTRICT: Reject updating or deleting a record from the parent table if there are records in the child table.

CASCADE: Updates or deletes records from the child table automatically by updating or deleting a record from the parent table.

SET NULL: Sets null as the field value in the daughter table, when updating or deleting the record from the parent table.

IN ACTION: Equivalent to the RESTRICT.

There is still the SET DEFAULT: Sets the value of the column in the daughter table, as the value set as default for it, when deleting or updating a record in the parent table.

more information: https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html

Examples: You have a City table and a Client table, Supposing:

Cidades:
id|Nome
 1|São Paulo

Cliente:
id|Nome  |Cidade_id
 1|Fulano|1

When updating / delete the registration of the city of sao paulo:

RESTRICT/NO ACTION: the bank will reject the command, returning a foreign key breach exception.

CASCADE: If you change the value of the City id column, the value of the Citade_id column in the Client table will also be changed. If São Paulo city is excluded, Customer 1, Fulano.

SET NULL: The value of the Cidade_id column of the records that are using the value 1, São Paulo, will be defined as null.

Complementing: What these options influence in my database?

Maintains the integrity of data.