Olá!
Para que a integridade do banco de dados (DB) seja mantida, há algumas regras que auxiliam a proteger as associações de registros que são realizadas entres as tabelas. Neste post será demonstrado algumas dessas regras que possuem relação quanto a exclusão registros que possuem ligação em outra tabela.
De modo, há apresentar o impacto que cada regra possui sobre a associação das tabelas e seus respectivos registros.
Temos, as seguintes regras:
- Delete Restrict
Não permite deletar registros "pai" que possuem ligação (por meio de FK) em outra tabela.
- Delete Cascade
Deleta o registro na "tabela pai" e automaticamente todos os demais registros associados a ele nas demais tabelas do DB.
- Delete SET NULL
Ao deletar o registro na "tabela pai" que está sendo referenciado em outras tabelas, a coluna(s) de ligação (FK) na outra tabela tem o valor alterado nulo (já que a FK não vai existir mais, pois o registro pai foi deletado).
Para os exemplos, é utilizado a tabela carro e marca_carro, sendo que elas estão ligadas pela coluna cd_marca_carro. Deste modo, vários carros podem ser de uma marca. Veja abaixo o modelo de entidade e relacionamento (EER) das tabelas:
 |
| Modelo EER para carro e marca_carro |
Neste modelo podemos deletar qualquer registro da tabela carro ("tabela filha") sem complicações. Entretanto, ao executar um SQL para excluir alguma marca de carro que esteja associada a algum carro será aplicado a regra definida na FK da tabela carro.
A tabela marca_carro neste modelo possui a ligação de ser a "tabela pai", assim ela foi criada primeiro com a seguinte instrução SQL:
CREATE TABLE `blog`.`marca_carro`
(
`cd_marca_carro`
INT NOT NULL
, `nm_marca_carro`
VARCHAR(45) NOT NULL
, PRIMARY KEY (`cd_marca_carro`));
Já a tabela carro, foi criada posteriormente com o SQL:
CREATE TABLE `blog`.`carro`
(
`cd_carro` INT NOT NULL
, `nm_carro` VARCHAR(45) NOT NULL
, `cd_marca_carro`
INT NOT NULL
, PRIMARY KEY (`cd_carro`)
, INDEX `cd_marca_carro_idx`
(`cd_marca_carro` ASC)
CONSTRAINT `cd_marca_carro`
FOREIGN KEY (`cd_marca_carro`)
REFERENCES `blog`.`marca_carro`
(`cd_marca_carro`)
ON DELETE RESTRICT
ON UPDATE NO ACTION);
A definição da regra a ser aplicada caso seja executado uma instrução DELETE na tabela marca_carro está neste caso na penúltima linha: ON DELETE RESTRICT.
Com os seguintes registros abaixo em cada tabela, é demonstrado os exemplos do impacto de cada regra ao ser executado instruções DELETE:
|
|
Note que o primeiro e segundo registro da tabela carro está associado a marca Fiat, e o carro com código 3 está associado a marca Jeep. Também, veja que não há nenhum carro associado a marca Mercedes.
- Impacto com a regra Delete Restrict
Ao executar o comando para deletar a marca Fiat que possui associação com dois carros, SQL:
DELETE FROM marca_carro WHERE
cd_marca_carro = 1;
Resultado:
Não possível deletar o registro de marca_carro, pois ele está sendo referenciado na tabela carro. Para que o SQL Delete aconteça, é necessário primeiramente que os registros que fazem referencia ao registro da tabela marca_carro sejam deletados ou atualizados para fazer referência a outro registro. Ou seja, o registro da tabela marca_carro somente será deletado se não estiver sendo referenciado em outra tabela.
Já ao executar o comando para excluir a marca Mercedes que não tem nenhuma ligação, SQL:
DELETE FROM marca_carro WHERE
cd_marca_carro = 3;
Resultado:
Deletado com sucesso pois não está sendo referenciado em nenhuma tabela.
Assim, após a execução das instruções Delete, a tabela marca_carro fica com os seguintes registros:
 |
| Tabela: marca_carro |
- Impacto com a regra Delete Cascade
Ao executar o comando para deletar a marca Fiat, SQL:
DELETE FROM marca_carro WHERE
cd_marca_carro = 1;
Automaticamente todos os registros da tabela carro vinculados a marca Fiat também são deletados, veja o estado de cada tabela após a execução do SQL :
Tabela marca_carro tem a marca Fiat deletada com sucesso:
 |
| Tabela: marca_carro |
A tabela carro possui a exclusão automática de todos os registros associados a marca deletada:
 |
| Tabela: carro |
- Impacto com a regra Delete SET NULL
Primeiramente é necessário habilitar que a coluna cd_marca_carro na tabela carro possa receber valor nulo, pois note que no comando de criação da tabela há a definição de NOT NULL para essa coluna.
Ao executar o SQL:
DELETE FROM marca_carro WHERE cd_marca_carro = 1;
A marca de código 1 é deletada com sucesso, e automaticamente todos os registros da tabela carro que faziam referência ao registro deletado teve o valor da coluna cd_marca_carro alterado para nulo, conforme:
 |
| Tabela: carro |
Além dessas três regras, existe o SET DEFAULT que é semelhante ao SET NULL, porem atribui um valor definido para a FK dos registros associados ao registro "pai" deletado.
E por fim, se nenhuma dessas regras forem atribuídas, a definição da tabela constará com:
ON DELETE NO ACTION. Assim, nenhuma ação/verificação é executada.
Observação: verifique se o mecanismo de armazenamento do DB permite a utilização dessas regras. Pois, por exemplo, o MySql com InnoDB não permite usar ON DELETE SET DEFAULT.
Consultar a relação de Termos e Abreviaturas acesse aqui.
FELIPE BARBOSA FERREIRA
Até o próximo post!
