MySQL EM DELETE CASCADE

Resumo : neste tutorial, você aprenderá como usar   ON DELETE CASCADE a ação referencial do MySQL para uma chave estrangeira para excluir dados de várias tabelas relacionadas.

No tutorial anterior, você aprendeu como excluir dados de diversas tabelas relacionadas usando uma única DELETEinstrução. No entanto, o MySQL fornece uma maneira mais eficaz chamada ON DELETE CASCADEação referencial para uma chave estrangeira que permite excluir dados de tabelas filho automaticamente quando você exclui os dados da tabela pai.

ON DELETE CASCADEExemplo de MySQL

Vamos dar uma olhada em um exemplo de uso do MySQL ON DELETE CASCADE.

Suponha que temos duas tabelas: buildingse rooms. Neste modelo de banco de dados, cada edifício possui um ou mais quartos. No entanto, cada quarto pertence a um único edifício. Uma sala não existiria sem um edifício.

O relacionamento entre as tabelas buildings e roomsé um para muitos (1:N), conforme ilustrado no diagrama de banco de dados a seguir:

Ao excluir uma linha da buildingstabela, você também deseja excluir todas as linhas da rooms  tabela que fazem referência à linha da buildingstabela. Por exemplo, quando você exclui uma linha com o número do edifício. 2 na buildings  tabela como a seguinte consulta:

DELETE FROM buildings 
WHERE building_no = 2;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Você também deseja que as linhas da roomstabela referentes ao edifício número 2 também sejam removidas.

A seguir estão as etapas que demonstram como a ON DELETE CASCADE  ação referencial funciona.

Passo 1 . Crie a buildingstabela:

CREATE TABLE buildings (
    building_no INT PRIMARY KEY AUTO_INCREMENT,
    building_name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Passo 2 . Crie a roomstabela:

CREATE TABLE rooms (
    room_no INT PRIMARY KEY AUTO_INCREMENT,
    room_name VARCHAR(255) NOT NULL,
    building_no INT NOT NULL,
    FOREIGN KEY (building_no)
        REFERENCES buildings (building_no)
        ON DELETE CASCADE
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Observe que a ON DELETE CASCADE  cláusula no final da definição da restrição de chave estrangeira .

Etapa 3 . Insira linhas na buildingstabela:

INSERT INTO buildings(building_name,address)
VALUES('ACME Headquaters','3950 North 1st Street CA 95134'),
      ('ACME Sales','5000 North 1st Street CA 95134');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Passo 4 . Consultar dados da buildingstabela:

SELECT * FROM buildings;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Temos duas linhas na buildingstabela.

Etapa 5 . Insira linhas na roomstabela:

INSERT INTO rooms(room_name,building_no)
VALUES('Amazon',1),
      ('War Room',1),
      ('Office of CEO',1),
      ('Marketing',2),
      ('Showroom',2);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Etapa 6 . Consultar dados da roomstabela:

SELECT * FROM rooms;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Temos três quartos que pertencem ao edifício nº 1 e dois quartos que pertencem ao edifício nº 2.

Etapa 7 . Exclua o prédio com o nº de prédio. 2:

DELETE FROM buildings 
WHERE building_no = 2;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Etapa 8 . Consultar dados da roomstabela:

SELECT * FROM rooms;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Como você pode ver, todas as linhas referentes a  building_no2 foram excluídas automaticamente.

Observe que ON DELETE CASCADE  funciona apenas com tabelas com  mecanismos de armazenamento que suportam chaves estrangeiras, por exemplo, InnoDB.

Alguns tipos de tabela não suportam chaves estrangeiras, como MyISAM, portanto, você deve escolher mecanismos de armazenamento apropriados para as tabelas que planeja usar na ON DELETE CASCADE  ação referencial do MySQL.

Dicas para encontrar tabelas afetadas pela ON DELETE CASCADEação do MySQL

Às vezes, é útil saber qual tabela é afetada pela ON DELETE CASCADE  ação referencial quando você exclui dados de uma tabela. Você pode consultar esses dados no referential_constraintsbanco information_schema  de dados da seguinte maneira:

USE information_schema;

SELECT 
    table_name
FROM
    referential_constraints
WHERE
    constraint_schema = 'database_name'
        AND referenced_table_name = 'parent_table'
        AND delete_rule = 'CASCADE'Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Por exemplo, para localizar tabelas associadas à buildingstabela com a CASCADE  regra de exclusão no classicmodelsbanco de dados, use a seguinte consulta:

USE information_schema;

SELECT 
    table_name
FROM
    referential_constraints
WHERE
    constraint_schema = 'classicmodels'
        AND referenced_table_name = 'buildings'
        AND delete_rule = 'CASCADE'Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Neste tutorial, você aprendeu como usar a ON DELETE CASCADE  ação referencial do MySQL para uma chave estrangeira para excluir dados automaticamente das tabelas filho ao excluir dados da tabela pai.