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 customers
do orders
banco de dados de exemplo .
Neste diagrama, cada cliente pode ter zero ou muitos pedidos e cada pedido pertence a um cliente.
O relacionamento entre customers
tabela e orders
tabela é um para muitos . Este relacionamento é estabelecido através da chave estrangeira na orders
tabela especificada pela customerNumber
coluna.
A customerNumber
coluna da orders
tabela está vinculada à customerNumber
coluna de chave primária da customers
tabela.
A customers
tabela é chamada de tabela pai ou tabela referenciada , e a orders
tabela é 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 NULL
exemplo de ação abaixo).
Por exemplo, cada linha da orders
tabela possui um customerNumber
que existe na customerNumber
coluna da customers
tabela. Várias linhas na orders
tabela 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 employees
tabela a seguir do banco de dados de amostra .
A reportTo
coluna é uma chave estrangeira que se refere à employeeNumber
coluna que é a chave primária da employees
tabela.
Esse relacionamento permite que a employees
tabela 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 TABLE
ou 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 CONSTRAINT
palavra-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 KEY
palavras-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 DELETE
e ON UPDATE
. O reference_option
determina 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
, RESTRICT
e 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
: CASCADE
e SET NULL
.
Se você não especificar a cláusula ON DELETE
and ON UPDATE
, a ação padrão será RESTRICT
.
Exemplos de CHAVE ESTRANGEIRA do MySQL
Vamos criar um novo banco de dados chamado fkdemo
para 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 fkdemo
banco de dados, crie duas tabelas categories
e 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 categoryId
na products
tabela é a coluna de chave estrangeira que se refere à categoryId
coluna da categories
tabela.
Como não especificamos nenhuma cláusula ON UPDATE
and ON DELETE
, a ação padrão é RESTRICT
para operações de atualização e exclusão.
As etapas a seguir ilustram a RESTRICT
ação.
1) Insira duas linhas na categories
tabela:
INSERT INTO categories(categoryName)
VALUES
('Smartphone'),
('Smartwatch');
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
2) Selecione os dados da categories
tabela:
SELECT * FROM categories;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
3) Insira uma nova linha na products
tabela:
INSERT INTO products(productName, categoryId)
VALUES('iPhone',1);
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Funciona porque o categoryId
1 existe na categories
tabela.
4) Tentativa de inserir uma nova linha na products
tabela com um categoryId
valor que não existe na categories
tabela:
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 categoryId
coluna da categories
tabela 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 RESTRICT
opção, você não pode excluir ou atualizar, categoryId 1
pois ela é referenciada por productId
1
na products
tabela.
2) Ação CASCATA
Essas etapas ilustram como ON UPDATE CASCADE
e ON DELETE CASCADE
as ações funcionam.
1) Solte a products
mesa:
DROP TABLE products;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
2) Crie a products
tabela com as opções ON UPDATE CASCADE
e ON DELETE CASCADE
para 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 products
tabela:
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 products
tabela:
SELECT * FROM products;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
5) Atualize categoryId
de 1 a 100 na categories
tabela:
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 products
tabela:
SELECT * FROM products;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Como você pode ver, duas linhas com valor 1
na categoryId
coluna da products
tabela foram atualizadas automaticamente 100
devido à ON UPDATE CASCADE
ação.
8) Exclua categoryId
2 da categories
tabela:
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 products
tabela:
SELECT * FROM products;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Todos os produtos com categoryId
2 da products
tabela foram excluídos automaticamente por causa da ON DELETE CASCADE
ação.
3) Ação DEFINIR NULL
Estas etapas ilustram como as ações ON UPDATE SET NULL
e ON DELETE SET NULL
funcionam.
1) Elimine ambas categories
as products
tabelas 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 categories
e 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 products
tabela mudou para opções ON UPDATE SET NULL
e ON DELETE SET NULL
.
3) Insira linhas na categories
tabela:
INSERT INTO categories(categoryName)
VALUES
('Smartphone'),
('Smartwatch');
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
4) Insira linhas na products
tabela:
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 categoryId
de 1 a 100 na categories
tabela:
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 products
tabela:
As linhas com categoryId
1 na products
tabela foram definidas automaticamente NULL
devido à ON UPDATE SET NULL
ação.
8) Exclua o categoryId
2 da categories
tabela:
DELETE FROM categories
WHERE categoryId = 2;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
9) Confira a products
tabela:
SELECT * FROM products;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Os valores na categoryId
coluna das linhas com categoryId
2 na products
tabela foram automaticamente definidos NULL
devido à ON DELETE SET NULL
ação.
Eliminar restrições de chave estrangeira do MySQL
Para eliminar uma restrição de chave estrangeira, você usa a ALTER TABLE
instruçã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 TABLE
palavras-chave. - Segundo, especifique o nome da restrição após as
DROP FOREIGN KEY
palavras-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 TABLE
instrução:
SHOW CREATE TABLE table_name;
Linguagem de código: SQL (linguagem de consulta estruturada) ( sql )
Por exemplo, para ver as chaves estrangeiras da products
tabela, 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 products
table tem uma restrição de chave estrangeira:fk_category
Esta instrução elimina a restrição de chave estrangeira da products
tabela:
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.