Bóson Treinamentos em Ciência e Tecnologia

ON DELETE CASCADE e Opções de Chave Estrangeira no MySQL

Fábio dos Reis
ON DELETE CASCADE e opções para chaves estrangeiras no MySQL

Opções de Chave Estrangeira no MySQL (e MariaDB)

Existem algumas opções aplicáveis às chaves estrangeiras que auxiliam a manter a integridade dos dados nas tabelas do banco de dados. Vamos relembrar a sintaxe SQL para criação de uma chave estrangeira em uma definição de tabela:

[CONSTRAINT nome_chave_estrangeira] FOREIGN KEY (nomes de colunas separados por vírgulas)
REFERENCES nome_tabela_pai (nomes de colunas separados por vírgulas na tabela pai)
[ON DELETE ação referencial]
[ON UPDATE ação referencial];

Os itens entre colchetes [ ] são opcionais. ON DELETE significa que a ação referencial será executada quando um registro for excluído da tabela pai, e ON UPDATE indica que a ação referencial será executada quando um registro for modificado na tabela pai.

As principais opções para as ações referenciais são as seguintes:

Vejamos um exemplo usando a cláusula ON DELETE CASCADE, que é uma das mais comuns usadas em chaves estrangeiras. Todos os exemplos mostrados aqui também podem ser utilizados com a cláusula ON UPDATE e, na prática, podemos usar ambas as cláusulas na mesma tabela.
Para isso, vamos criar um banco de dados de nome “testes”, contendo duas tabelas relacionadas, chamadas de “Pai” e “Filho”, conforme a seguinte estrutura:

Códigos SQL para criar o banco de teste e as tabelas:

CREATE DATABASE testes;
USE testes;

CREATE TABLE Pai (
 ID_Pai SMALLINT PRIMARY KEY,
 Nome_Pai VARCHAR(50)
);

CREATE TABLE Filho (
 ID_Filho SMALLINT AUTO_INCREMENT PRIMARY KEY,
 Nome_Filho VARCHAR(50),
 ID_Pai SMALLINT,
 CONSTRAINT fk_id_pai FOREIGN KEY (ID_Pai)
 REFERENCES Pai(ID_Pai)
 ON DELETE CASCADE
 ON UPDATE CASCADE
);

Carregando dados de teste nas tabelas:

INSERT INTO Pai
VALUES (1,'João'), (2,'Mário'), (3,'Renato'), (4,'Emerson'), (5,'André');
INSERT INTO Filho (Nome_Filho, ID_Pai)
VALUES ('João',1), ('Mário',1), ('Renato',3), ('Emerson',4), ('André',3);

Consultando os dados carregados:

SELECT P.ID_Pai, P.Nome_Pai, F.ID_Filho, F.Nome_Filho
FROM Filho F
INNER JOIN Pai P
ON F.ID_Pai = P.ID_Pai;

Vamos testar agora a exclusão de um filho:

DELETE FROM Filho
WHERE Nome_Filho = 'Renato';

Ao excluirmos o filho Renato, seu pai, que também se chama Renato, continuará a existir na tabela de pais:

SELECT Nome_Pai, Nome_Filho
FROM Filho
INNER JOIN Pai
ON Filho.ID_Pai = Pai.ID_Pai;

Agora vamos testar a cláusula ON DELETE CASCADE. Vamos excluir o Pai Renato da tabela de pais. Neste caso, a exclusão deverá se propagar para a tabela de filhos, eliminando o registro do filho relacionado, que no caso é o André:

DELETE FROM Pai
WHERE Nome_Pai = 'Renato';

Verificando a exclusão do pai:

SELECT Nome_Pai, Nome_Filho
FROM Filho
INNER JOIN Pai
ON Filho.ID_Pai = Pai.ID_Pai;

Verificando a exclusão cascateada do filho:

SELECT * FROM Filho;

Agora sobraram apenas os filhos João, Mário e Emerson; Já o André, que era filho do Renato, foi excluído automaticamente após eliminarmos o registro de seu pai da tabela de pais, devido à cláusula ON DELETE CASCADE.

Vejamos agora um exemplo usando SET NULL

Exemplo com SET NULL

Suponha que, ao excluir um pai do banco de dados, em vez de excluir imediatamente seus filhos (cascateamento) nós queiramos manter esses registros, e o campo de ID_Pai da tabela de filhos passe então a conter um valor NULL (“filhos órfãos”).

Neste caso, a tabela de filhos deve ser criada da maneira mostrada a seguir, substituindo a cláusula ON DELETE CASCADE por ON DELETE SET NULL:

DROP TABLE Filho;
DROP TABLE Pai;

CREATE TABLE Pai (
 ID_Pai SMALLINT PRIMARY KEY,
 Nome_Pai VARCHAR(50)
) Engine=InnoDB;
CREATE TABLE Filho (
 ID_Filho SMALLINT AUTO_INCREMENT PRIMARY KEY,
 Nome_Filho VARCHAR(50),
 ID_Pai SMALLINT,
 CONSTRAINT fk_id_pai FOREIGN KEY (ID_Pai)
 REFERENCES Pai(ID_Pai)
 ON DELETE SET NULL
 ON UPDATE CASCADE
) Engine=InnoDB;

INSERT INTO Pai
VALUES (1,'João'), (2,'Mário'), (3,'Renato'), (4,'Emerson'), (5,'André');
INSERT INTO Filho (Nome_Filho, ID_Pai)
VALUES ('João',1), ('Mário',1), ('Renato',3), ('Emerson',4), ('André',3);

Após recriar a tabela de filhos e populá-la novamente, com a nova opção de chave estrangeira, vamos realizar um teste excluindo um dos pais da tabela de pais – por exemplo, novamente o Renato:

DELETE FROM Pai
WHERE Nome_Pai = 'Renato';

E agora verificamos os registros na tabela de filhos:

SELECT * FROM Filho;

Note que após excluir o pai, seus filhos, Renato e André mostram o valor “NULL” na coluna ID_Pai da tabela de filhos. Mas eles não foram excluídos automaticamente, como aconteceu ao usarmos CASCADE no exemplo anterior.

A coluna não pode ter sido criada com a restrição NOT NULL durante a definição da tabela, ou este comando irá gerar uma exceção.

Usando valor padrão – SET DEFAULT

Outra opção seria a de exibir um valor padrão na coluna quando o valor relacionado for atualizado ou excluído. Para isso, definimos o valor padrão para uma coluna usando a restrição DEFAULT, e então acrescentamos a ação referencial SET DEFAULT à declaração da chave estrangeira.

Por exemplo, ao excluirmos um pai da tabela de pais, em vez de exibir valor NULL na tabela de filhos, poderíamos exibir o valor “0” (zero), simbolizando um órfão, na coluna ID_Pai desta tabela.

Porém, apesar de a ação referencial SET DEFAULT ser suportada pelo MySQL Server, ela é rejeitada como inválida pelo motor de banco de dados InnoDB – que é o motor padrão do MySQL. Um motor de banco de dados que suporta essa ação é o PBXT, um motor que não é  fornecido como parte integrante do MySQL (nem do MariaDB).

Bibliografia:

 

Sair da versão mobile