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

Question

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

Navigation

#1 by (18 votes)

14

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 the change and deleting records.

The options are:

RESTRICT

CASCADE

SET NULL

NO ACTION

A practical illustration example 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.

Questions

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

mysql sql

asked by anonymous 09.06.2017 / 19:16

1 answer

What's the difference between using (int) variable or Convert.ToInt32 (variable)? What is the problem of queries N + 1?

score 18 · Accepted Answer

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

RESTRICT: Refuses to update or delete a record from the parent table if there are records in the child table.

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

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

NO ACTION: Equivalent to RESTRICT.

There is still the SET DEFAULT: Sets the value of the column in the child table, as the value set to default by deleting or updating a record in the parent table.

More information: link

Examples: You have a City and Client 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's id column, the value of the City_id column in the Customer table will also be changed. If you exclude the city of São Paulo, you will also be excluded Client 1, So-and-so.

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

Complementing: What do these options influence in my database?

Maintains data integrity.