Question:

When I'm going to create a foreign key type relationship between two tables in MySQL, I can specify some additional options in the ON UPDATE and ON DELETE events that are associated with changing and deleting records.

The options are:

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

A practical example of illustration using the NO ACTION option, 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 questions about these options.

Doubts

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

Answer:

These are options for foreign keys, trying to simplify as much as possible:

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

CASCADE: Automatically updates or deletes records in the child table when updating or deleting a record in the parent table.

SET NULL: Sets the value of the field in the child table to null when updating or deleting the record in the parent table.

NO ACTION: Equivalent to RESTRICT.

There is also SET DEFAULT: Sets the column's value in the child table as the default value 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 Customer table, Assuming:

Cidades:
id|Nome
 1|São Paulo

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

When updating / deleting the registration of the city of são paulo:

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

CASCADE: If you change the value of the City id column, the City_id column value in the Customer table will also be changed. If you exclude the city of São Paulo, Client 1, so-and-so will also be excluded.

SET NULL: The value of the Cidade_Id column of the records that are using the value 1, São Paulo, will be set to null.

Complementing: How do these options influence my database?

Maintains data integrity.