MySQL desabilita verificações de chave estrangeira

Resumo : neste tutorial, você aprenderá como desabilitar verificações de restrição de chave estrangeira no MySQL.

Às vezes, é muito útil desabilitar as verificações de chave estrangeira . Por exemplo, você pode carregar dados nas tabelas pai e filho em qualquer ordem com a verificação de restrição de chave estrangeira desabilitada.

Se você não desabilitar as verificações de chave estrangeira, terá que carregar os dados primeiro nas tabelas pai e depois nas tabelas filhas em sequência, o que pode ser entediante.

Outro cenário em que você deseja desabilitar a verificação de chave estrangeira é quando deseja eliminar uma tabela . A menos que você desabilite as verificações de chave estrangeira, não será possível eliminar uma tabela referenciada por uma restrição de chave estrangeira.

Para desativar as verificações de chave estrangeira, defina a foreign_key_checksvariável como zero da seguinte maneira:

SET foreign_key_checks = 0;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Para ativar a verificação de restrição de chave estrangeira, defina o valor de foreign_key_checkscomo 1:

SET foreign_key_checks = 1;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Observe que definir foreign_key_checkscomo 1 não aciona nenhuma validação dos dados da tabela existente. Em outras palavras, o MySQL não verificará a consistência dos dados que foram adicionados durante a verificação da chave estrangeira desativada.

Exemplo de desabilitar verificação de chave estrangeira

Primeiro, crie uma nova tabela chamada countries:

CREATE TABLE countries(
    country_id INT AUTO_INCREMENT,
    country_name VARCHAR(255) NOT NULL,
    PRIMARY KEY(country_id)
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Segundo, crie outra tabela chamada cities:

CREATE TABLE cities(
    city_id INT AUTO_INCREMENT,
    city_name VARCHAR(255) NOT NULL,
    country_id INT NOT NULL,
    PRIMARY KEY(city_id),
    FOREIGN KEY(country_id) 
		REFERENCES countries(country_id)
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

A tabela citiespossui uma restrição de chave estrangeira que faz referência à coluna country_idda tabela countries.

Terceiro, insira uma nova linha na citiestabela:

INSERT INTO cities(city_name, country_id)
VALUES('New York',1);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

MySQL emitiu o seguinte erro:

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`classicmodels`.`cities`, CONSTRAINT `cities_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `countries` (`country_id`))Linguagem de código:  JavaScript  ( javascript )

Quarto, desative as verificações de chave estrangeira:

SET foreign_key_checks = 0;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Quinto, insira uma nova linha na citiestabela:

INSERT INTO cities(city_name, country_id)
VALUES('New York',1);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Desta vez, a INSERTinstrução foi executada com sucesso devido à desativação da verificação da chave estrangeira.

A consulta a seguir retorna o conteúdo da tabela cities:

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

Sexto, reative a verificação de restrição de chave estrangeira:

SET foreign_key_checks = 1;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Quando as verificações de chave estrangeira foram reativadas, o MySQL não revalidou os dados na tabela. No entanto, não permitirá inserir ou atualizar dados que violem a restrição de chave estrangeira.

Por fim, insira uma linha na countriestabela cujo valor na coluna country_idseja 1 para tornar os dados consistentes em ambas as tabelas:

INSERT INTO countries(country_id, country_name)
VALUES(1,'USA');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Eliminar tabelas que possuem restrições de chave estrangeira

Suponha que você queira eliminar as tabelas countriese cities.

Primeiro, elimine a tabela countries:

DROP TABLE countries;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

MySQL emitiu este erro:

Error Code: 3730. Cannot drop table 'countries' referenced by a foreign key constraint 'cities_ibfk_1' on table 'cities'.Linguagem de código:  JavaScript  ( javascript )

Para corrigir isso, você tem duas opções:

• Solte a tabela citiesprimeiro e depois remova-a countries.
• Desative verificações de chave estrangeira e elimine tabelas em qualquer sequência.

Demonstraremos a segunda maneira que desativa a verificação de restrição de chave estrangeira antes de descartar as tabelas.

Segundo, desative a verificação de chave estrangeira:

SET foreign_key_checks = 0;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Terceiro, elimine ambas as tabelas countriese cities:

DROP TABLE countries;
DROP TABLE cities;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Ambas as instruções foram executadas com sucesso.

Finalmente, habilite a verificação de chave estrangeira:

SET foreign_key_checks = 1;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Resumo

• Use SET foreign_key_checks = 0 para desabilitar verificações de chave estrangeira no MySQL.