Resumo : neste tutorial, você aprenderá sobre a chave estrangeira do MySQL e como criar, eliminar e desabilitar uma restrição de chave estrangeira.
Introdução à chave estrangeira MySQL
Uma chave estrangeira é uma coluna ou grupo de colunas em uma tabela vinculada a uma coluna ou grupo de colunas em outra tabela. A chave estrangeira impõe restrições aos dados nas tabelas relacionadas, o que permite ao MySQL manter a integridade referencial.
Vamos dar uma olhada nas tabelas a seguir customersdo ordersbanco de dados de exemplo .
Neste diagrama, cada cliente pode ter zero ou muitos pedidos e cada pedido pertence a um cliente.
O relacionamento entre customerstabela e orderstabela é um para muitos . Este relacionamento é estabelecido através da chave estrangeira na orderstabela especificada pela customerNumbercoluna.
A customerNumbercoluna da orderstabela está vinculada à customerNumbercoluna de chave primária da customerstabela.
A customerstabela é chamada de tabela pai ou tabela referenciada , e a orderstabela é conhecida como tabela filha ou tabela de referência .
Normalmente, as colunas de chave estrangeira da tabela filho geralmente se referem às colunas de chave primária da tabela pai.
Uma tabela pode ter mais de uma chave estrangeira, onde cada chave estrangeira faz referência a uma chave primária das diferentes tabelas pai.
Depois que uma restrição de chave estrangeira estiver em vigor, as colunas de chave estrangeira da tabela filho deverão ter a linha correspondente nas colunas de chave pai da tabela pai, ou os valores nessas colunas de chave estrangeira deverão ter NULL(veja o SET NULLexemplo de ação abaixo).
Por exemplo, cada linha da orderstabela possui um customerNumberque existe na customerNumbercoluna da customerstabela. Várias linhas na orderstabela podem ter o mesmo arquivo customerNumber.
Chave estrangeira de autorreferência
Às vezes, as tabelas filho e pai podem referir-se à mesma tabela. Nesse caso, a chave estrangeira faz referência à chave primária na mesma tabela.
Consulte a employeestabela a seguir do banco de dados de amostra .
A reportTocoluna é uma chave estrangeira que se refere à employeeNumbercoluna que é a chave primária da employeestabela.
Esse relacionamento permite que a employeestabela armazene a estrutura de subordinação entre funcionários e gerentes. Cada funcionário se reporta a zero ou a um funcionário e um funcionário pode ter zero ou muitos subordinados.
A chave estrangeira na coluna reportToé conhecida como chave estrangeira recursiva ou de auto-referência .
Sintaxe MySQL FOREIGN KEY
Aqui está a sintaxe básica para definir uma restrição de chave estrangeira na instrução CREATE TABLEou ALTER TABLE:
[CONSTRAINT constraint_name]
FOREIGN KEY [foreign_key_name] (column_name, ...)
REFERENCES parent_table(colunm_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Nesta sintaxe:
Primeiro, especifique o nome da restrição de chave estrangeira que deseja criar após a CONSTRAINTpalavra-chave. Se você omitir o nome da restrição, o MySQL gera automaticamente um nome para a restrição de chave estrangeira.
Segundo, especifique uma lista de colunas de chave estrangeira separadas por vírgula após as FOREIGN KEYpalavras-chave. O nome da chave estrangeira também é opcional e é gerado automaticamente se você ignorá-lo.
Terceiro, especifique a tabela pai seguida por uma lista de colunas separadas por vírgula às quais as colunas de chave estrangeira fazem referência.
Finalmente, especifique como a chave estrangeira mantém a integridade referencial entre as tabelas filha e pai usando as cláusulas ON DELETEe ON UPDATE. O reference_optiondetermina a ação que o MySQL executará quando os valores nas colunas da chave pai forem excluídos ( ON DELETE) ou atualizados ( ON UPDATE).
MySQL tem cinco opções de referência: CASCADE, SET NULL, NO ACTION, RESTRICTe SET DEFAULT.
CASCADE: se uma linha da tabela pai for excluída ou atualizada, os valores das linhas correspondentes na tabela filho serão automaticamente excluídos ou atualizados.SET NULL: se uma linha da tabela pai for excluída ou atualizada, os valores da coluna (ou colunas) de chave estrangeira na tabela filho serão definidos comoNULL.RESTRICT: se uma linha da tabela pai tiver uma linha correspondente na tabela filho, o MySQL rejeitará a exclusão ou atualização de linhas na tabela pai.NO ACTION: é o mesmo queRESTRICT.SET DEFAULT: é reconhecido pelo analisador MySQL. No entanto, esta ação é rejeitada pelas tabelas InnoDB e NDB.
O MySQL oferece suporte total a três ações RESTRICT: CASCADEe SET NULL.
Se você não especificar a cláusula ON DELETEand ON UPDATE, a ação padrão será RESTRICT.
Exemplos de CHAVE ESTRANGEIRA do MySQL
Vamos criar um novo banco de dados chamado fkdemopara demonstração.
CREATE DATABASE fkdemo;
USE fkdemo;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )1) Ações RESTRITAS E SEM AÇÃO
Dentro do fkdemobanco de dados, crie duas tabelas categoriese products:
CREATE TABLE categories(
categoryId INT AUTO_INCREMENT PRIMARY KEY,
categoryName VARCHAR(100) NOT NULL
) ENGINE = INNODB;
CREATE TABLE products(
productId INT AUTO_INCREMENT PRIMARY KEY,
productName VARCHAR(100) NOT NULL,
categoryId INT,
CONSTRAINT fk_category FOREIGN KEY (categoryId)
REFERENCES categories(categoryId)
) ENGINE = INNODB;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )O categoryIdna productstabela é a coluna de chave estrangeira que se refere à categoryIdcoluna da categoriestabela.
Como não especificamos nenhuma cláusula ON UPDATEand ON DELETE, a ação padrão é RESTRICTpara operações de atualização e exclusão.
As etapas a seguir ilustram a RESTRICTação.
1) Insira duas linhas na categoriestabela:
INSERT INTO categories(categoryName)
VALUES
('Smartphone'),
('Smartwatch');Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )2) Selecione os dados da categoriestabela:
SELECT * FROM categories;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
3) Insira uma nova linha na productstabela:
INSERT INTO products(productName, categoryId)
VALUES('iPhone',1);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Funciona porque o categoryId1 existe na categoriestabela.
4) Tentativa de inserir uma nova linha na productstabela com um categoryId valor que não existe na categoriestabela:
INSERT INTO products(productName, categoryId)
VALUES('iPad',3);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 (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)Linguagem de código: JavaScript ( javascript )5) Atualize o valor na categoryIdcoluna da categoriestabela para 100:
UPDATE categories
SET categoryId = 100
WHERE categoryId = 1;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )MySQL emitiu este erro:
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`fkdemo`.`products`, CONSTRAINT `fk_category` FOREIGN KEY (`categoryId`) REFERENCES `categories` (`categoryId`) ON DELETE RESTRICT ON UPDATE RESTRICT)Linguagem de código: JavaScript ( javascript )Por causa da RESTRICTopção, você não pode excluir ou atualizar, categoryId 1pois ela é referenciada por productId 1na productstabela.
2) Ação CASCATA
Essas etapas ilustram como ON UPDATE CASCADEe ON DELETE CASCADEas ações funcionam.
1) Solte a productsmesa:
DROP TABLE products;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )2) Crie a productstabela com as opções ON UPDATE CASCADEe ON DELETE CASCADEpara a chave estrangeira:
CREATE TABLE products(
productId INT AUTO_INCREMENT PRIMARY KEY,
productName varchar(100) not null,
categoryId INT NOT NULL,
CONSTRAINT fk_category
FOREIGN KEY (categoryId)
REFERENCES categories(categoryId)
ON UPDATE CASCADE
ON DELETE CASCADE
) ENGINE=INNODB;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )3) Insira quatro linhas na productstabela:
INSERT INTO products(productName, categoryId)
VALUES
('iPhone', 1),
('Galaxy Note',1),
('Apple Watch',2),
('Samsung Galary Watch',2);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )4) Selecione os dados da productstabela:
SELECT * FROM products;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
5) Atualize categoryIdde 1 a 100 na categoriestabela:
UPDATE categories
SET categoryId = 100
WHERE categoryId = 1;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )6) Verifique a atualização:
SELECT * FROM categories;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
7) Obtenha os dados da productstabela:
SELECT * FROM products;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Como você pode ver, duas linhas com valor 1na categoryIdcoluna da productstabela foram atualizadas automaticamente 100devido à ON UPDATE CASCADEação.
8) Exclua categoryId2 da categoriestabela:
DELETE FROM categories
WHERE categoryId = 2;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )9) Verifique a exclusão:
SELECT * FROM categories;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
10) Confira a productstabela:
SELECT * FROM products;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Todos os produtos com categoryId2 da productstabela foram excluídos automaticamente por causa da ON DELETE CASCADEação.
3) Ação DEFINIR NULL
Estas etapas ilustram como as ações ON UPDATE SET NULLe ON DELETE SET NULLfuncionam.
1) Elimine ambas categoriesas productstabelas e:
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS products;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )2) Crie as tabelas categoriese products:
CREATE TABLE categories(
categoryId INT AUTO_INCREMENT PRIMARY KEY,
categoryName VARCHAR(100) NOT NULL
)ENGINE=INNODB;
CREATE TABLE products(
productId INT AUTO_INCREMENT PRIMARY KEY,
productName varchar(100) not null,
categoryId INT,
CONSTRAINT fk_category
FOREIGN KEY (categoryId)
REFERENCES categories(categoryId)
ON UPDATE SET NULL
ON DELETE SET NULL
)ENGINE=INNODB;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )A chave estrangeira na productstabela mudou para opções ON UPDATE SET NULLe ON DELETE SET NULL.
3) Insira linhas na categoriestabela:
INSERT INTO categories(categoryName)
VALUES
('Smartphone'),
('Smartwatch');Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )4) Insira linhas na productstabela:
INSERT INTO products(productName, categoryId)
VALUES
('iPhone', 1),
('Galaxy Note',1),
('Apple Watch',2),
('Samsung Galary Watch',2);Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )5) Atualize categoryIdde 1 a 100 na categoriestabela:
UPDATE categories
SET categoryId = 100
WHERE categoryId = 1;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )6) Verifique a atualização:
SELECT * FROM categories;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
7) Selecione os dados da productstabela:
As linhas com categoryId1 na productstabela foram definidas automaticamente NULLdevido à ON UPDATE SET NULLação.
8) Exclua o categoryId2 da categoriestabela:
DELETE FROM categories
WHERE categoryId = 2;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )9) Confira a productstabela:
SELECT * FROM products;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Os valores na categoryIdcoluna das linhas com categoryId2 na productstabela foram automaticamente definidos NULLdevido à ON DELETE SET NULLação.
Eliminar restrições de chave estrangeira do MySQL
Para eliminar uma restrição de chave estrangeira, você usa a ALTER TABLEinstrução:
ALTER TABLE table_name
DROP FOREIGN KEY constraint_name;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Nesta sintaxe:
- Primeiro, especifique o nome da tabela da qual deseja eliminar a chave estrangeira após as
ALTER TABLEpalavras-chave. - Segundo, especifique o nome da restrição após as
DROP FOREIGN KEYpalavras-chave.
Observe que constraint_nameé o nome da restrição de chave estrangeira especificada quando você criou ou adicionou a restrição de chave estrangeira à tabela.
Para obter o nome da restrição gerada de uma tabela, você usa a SHOW CREATE TABLEinstrução:
SHOW CREATE TABLE table_name;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Por exemplo, para ver as chaves estrangeiras da productstabela, você usa a seguinte instrução:
SHOW CREATE TABLE products;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )A seguir está a saída da instrução:
Como você pode ver claramente na saída, a tabela productstable tem uma restrição de chave estrangeira:fk_category
Esta instrução elimina a restrição de chave estrangeira da productstabela:
ALTER TABLE products
DROP FOREIGN KEY fk_category;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Para garantir que a restrição de chave estrangeira foi eliminada, você pode visualizar a estrutura da tabela de produtos:
SHOW CREATE TABLE products;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Desativando verificações de chave estrangeira
Às vezes, é muito útil desabilitar verificações de chave estrangeira, por exemplo, quando você importa dados de um arquivo CSV para uma tabela .
Se você não desabilitar as verificações de chave estrangeira, você terá que carregar os dados em uma ordem adequada, ou seja, você terá que carregar os dados primeiro nas tabelas pai e depois nas tabelas filhas, o que pode ser entediante.
No entanto, se você desabilitar as verificações de chave estrangeira, poderá carregar dados nas tabelas em qualquer ordem.
Para desabilitar verificações de chave estrangeira, use a seguinte instrução:
SET foreign_key_checks = 0;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )E você pode habilitá-lo usando a seguinte instrução:
SET foreign_key_checks = 1;Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )Neste tutorial, você aprendeu sobre a chave estrangeira do MySQL e como criar uma restrição de chave estrangeira com várias opções de referência.
