Comando ON CASCADE

Considere as tabelas criadas na aula anterior criadas com os seguintes comandos:

CREATE TABLE Clientes (
    id_cliente INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL
);

CREATE TABLE Pizzas (
    id_pizza INT AUTO_INCREMENT PRIMARY KEY,
    nome VARCHAR(100) NOT NULL,
    preco DECIMAL(10,2) NOT NULL
);

CREATE TABLE Pedidos (
    id_pedido INT AUTO_INCREMENT PRIMARY KEY,
    id_cliente INT NOT NULL,
    id_pizza INT NOT NULL,
    
    CONSTRAINT FK_Cliente FOREIGN KEY (id_cliente) REFERENCES Clientes(id_cliente),
    CONSTRAINT FK_Pizza FOREIGN KEY (id_pizza) REFERENCES Pizzas(id_pizza)
);

Podemos ver que a tabela de pedidos tem duas chaves estrangeiras: id_cliente e id_pizzas. Sabemos que isto quer dizer que a coluna id_cliente da tabela Pedidos faz uma referência à coluna id_cliente da tabela Clientes.

Mas o quê essas restrições influenciam na prática?[1]

Essas restrições previnem que existam dados inválido no bando de dados.

As consequências são duas restrições:

Primeira restrição

Não podemos incluir um pedido com um id_pizza que não existe.

Da direção da tabela filha (aquela que tem a restrição) para a tabela pai. Na tabela filha não podemos inserir uma entrada com uma referência à algo que não existe na tabela pai.

Exemplo

Vamos tentar incluir um Pedido e ver qual é a resposta do banco de dados:

INSERT INTO Pedidos(id_cliente, id_pizza) 
VALUES (10, 1);
Saída 
1452 - Cannot add or update a child row: a foreign key constraint fails (`bd_pizzaria_napoli`.`pedidos`, CONSTRAINT `FK_Cliente` FOREIGN KEY (`id_cliente`) REFERENCES `clientes` (`id_cliente`))

Ou seja: Cannot add or update a child row: a foreign key constraint fails: (não podemos adicionar ou atualizar uma linha filha: falha de chave estrangeira.)

Segunda restrição

Não podemos deletar ou modificar o id_cliente de um cliente que tenha, pelo menos, um pedido fazendo referência a ele.

Da direção da tabela pai (aquela para a qual alguma tabela faça referência) para a tabela filha. Na tabela pai não podemos modificar uma entrada onde alguma tabela filha faça referência a ela.

Vamos ver uma tentativa de modificação:

UPDATE Pizzas 
SET id_pizza=10
WHERE id_pizza=1;

1451 - Cannot delete or update a parent row: a foreign key constraint fails (`bd_pizzaria_napoli`.`pedidos`, CONSTRAINT `FK_Pizza` FOREIGN KEY (`id_pizza`) REFERENCES `pizzas` (`id_pizza`))

Neste caso Cannot delete or update a parent row: a foreign key constraint fails: (não podemos deletar ou modificar uma linha pai: uma chave estrangeira faz referência a ela)

Se tentarmos deletar teremos a mesma resposta.

DELETE FROM Pizzas 
WHERE id_pizza=1;

#1451 - Cannot delete or update a parent row: a foreign key constraint fails (`bd_pizzaria_napoli`.`pedidos`, CONSTRAINT `FK_Pizza` FOREIGN KEY (`id_pizza`) REFERENCES `pizzas` (`id_pizza`))

Soluções

A restrição que o banco de dados nos impõe é muito útil para proteger a consistência dos dados. Mas será que é possível uma situação onde precisamos anular estas restrições?

A primeira restrição não queremos resolver pois nunca será válido inserir uma linha que faz referência à uma entrada que não existe.

Porém, para a segunda podemos ter que pensar em uma solução.

E se precisarmos modificar ou apagar dados da tabela pai, como apagar uma pizza? Ou modificar o id de uma pizza?

Para isto teremos os comando ON CASCADE.

Opções ON DELETE e ON UPDATE

Quando definimos as restrições na tabela podemos escolher algumas opções em relação a apagar ON DELETE e à modificação ON UPDATE[2]

Sintaxe 
[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL

Vamos ver cada uma das opções:

Exemplo

Vamos criar um banco de dados chamado bd_pizzaria_napoli_cascade com as mesmas características da aula anterior, mas com as restrições de chave estrangeira com os valores ON DELETE CASCADE e ON UPDATE CASCADE:

CREATE TABLE Pedidos (
    id_pedido INT AUTO_INCREMENT PRIMARY KEY,
    id_cliente INT NOT NULL,
    id_pizza INT NOT NULL,
    
    CONSTRAINT FK_Cliente 
    FOREIGN KEY (id_cliente) 
    REFERENCES Clientes(id_cliente)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    
    CONSTRAINT FK_Pizza 
    FOREIGN KEY (id_pizza) 
    REFERENCES Pizzas(id_pizza)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

Agora vamos tentar as mesmas modificações.

Tentando quebrar a primeira restrição

No primeiro caso vamos tentar colocar uma entrada em Pedidos com um id inexistente:

INSERT INTO Pedidos(id_cliente, id_pizza) 
VALUES (10, 1);

Ainda teremos o mesmo erro, o que é bom, nunca queremos que seja possível fazer uma entrada com a referência quebrada.

#1452 - Cannot add or update a child row: a foreign key constraint fails (`bd_pizzaria_napoli_cascade`.`pedidos`, CONSTRAINT `FK_Cliente` FOREIGN KEY (`id_cliente`) REFERENCES `clientes` (`id_cliente`) ON DELETE CASCADE ON UPDATE CASCADE)
Tentando quebrar a segunda restrição

Na segunda restrição esperamos que consigamos modifica ou apagar sem causar uma inconsistência do banco de dados.

Então vamos ver a tabela pedidos e clientes

Clientes
id_cliente nome
1 Ana
2 Beto
3 Carla
Pedidos
id_pedido id_cliente id_pizza
1 1 1
2 1 1
3 1 2
4 2 1

Vamos tentar mudar o id_cliente da Ana para 10.

UPDATE Clientes
SET id_cliente=10
WHERE id_cliente=1;
Resultado
Clientes
id_cliente nome
2 Beto
3 Carla
10 Ana
Pedidos
id_pedido id_cliente id_pizza
1 10 1
2 10 1
3 10 2
4 2 1

Podemos ver que a modificação foi propagada para a tabela pedidos.

Agora vamos tentar apagar a cliente Ana.

DELETE FROM Clientes
WHERE id_cliente=10;
Resultado
Clientes
id_cliente nome
2 Beto
3 Carla
Pedidos
id_pedido id_cliente id_pizza
4 2 1

  1. fonte w3schools sobre restrições ↩︎

  2. fonte dev mysql ↩︎