basics mysql foreign key constraint with examples
Este tutorial explica os fundamentos da restrição MySQL FOREIGN KEY, como sua sintaxe, como adicionar, declarar, descartar e alterá-la com exemplos:
Em termos muito simples, a FOREIGN KEY é usada para vincular duas ou mais tabelas no MySQL.
As tabelas do MySQL precisam ser conectadas para consultar e atualizar vários tipos de dados em diferentes pontos no tempo. Portanto, é imperativo ter um ponto de ligação entre 2 tabelas.
Neste tutorial, discutiremos os diferentes usos de chaves estrangeiras e como elas podem ser declaradas e alteradas, e quais restrições têm na estrutura geral da tabela.
O que você aprenderá:
MySQL FOREIGN KEY
Sintaxe:
CONSTRAINT {constraintName} FOREIGN KEY (referringColumnName) REFERENCES {referredTable}({referredColumn}) ON UPDATE {reference-option} ON DELETE {reference-option}
Acima está a sintaxe usada ao especificar FOREIGN KEY contra uma tabela durante a criação da tabela ou com Instrução ALTER TABLE.
Vamos entender os diferentes componentes da sintaxe:
- constrantName: Este é o nome simbólico que queremos definir para a restrição FK que está sendo especificada. Se isso for ignorado, o mecanismo MySQL atribuirá automaticamente um nome à restrição FK.
- referenceColumnName: Esta é a coluna que se referiria aos valores em outra tabela conforme especificado pela coluna na tabela referida.
- Tabela referenciada / tabela pai: Isso se refere ao nome da tabela da qual os valores seriam referidos.
- Coluna Referida: O nome da coluna na tabela referida.
- Opção de referência: Essas são as ações que entram em cena quando uma ação de atualização ou exclusão é feita na tabela que contém a restrição de chave estrangeira. Tanto UPDATE quanto DELETE podem ter opções de referência iguais ou diferentes.
Aprenderíamos sobre diferentes ações de integridade referencial posteriormente neste tutorial.
Vamos ver um exemplo de referência de FOREIGN KEY usando o exemplo de Funcionário / Departamento. Criaremos uma tabela Department com colunas - departmentId (int & PRIMARY KEY) e departmentName (varchar).
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL, departmentName VARCHAR(100));
Crie uma tabela Employee com as colunas abaixo:
Coluna | Modelo |
---|---|
eu ia | INT (chave primária) |
nome | VARCHAR |
dept_id | INT (chave estrangeira) referenciada na tabela de departamento |
Morada | VARCHAR |
idade | INT |
dob | ENCONTRO |
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT, CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE);
Como você pode ver, na tabela Employee acima, declaramos a coluna deptId do tipo Int e definimos FOREIGN KEY da tabela Department na coluna departmentId.
O que isso significa essencialmente que a coluna deptId na tabela Employee pode conter apenas valores que estão na tabela Department.
Vamos tentar inserir dados nessas tabelas e ver como FOREIGN KEY CONSTRAINT funciona.
- Crie um registro na tabela Departamento primeiro e adicione um registro na tabela Funcionário fazendo referência ao ID do registro que foi adicionado à tabela Departamento.
INSERT INTO department VALUES (1, 'ENGINEERING') --------- INSERT INTO EMPLOYEE VALUES (1, 'AMIT KUMAR', 'MUMBAI', 32, '1988-02-12',1);
Você verá que ambas as instruções seriam executadas sem erros.
- Agora faça referência a um valor para departmentId que não existe.
Por exemplo, na declaração de consulta abaixo, estamos criando um Funcionário com um departamento ID -10 não existente
INSERT INTO EMPLOYEE VALUES (1, 'DARREN JOHNSON', 'CHICAGO', 32, '1988-02-12',10);
- Nesse cenário, obteremos um erro como a seguir:
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`my_sql_foreign_key`.`employee`, CONSTRAINT `depIdFk` FOREIGN KEY (`deptId`) REFERENCES `department` (`departmentId`) ON DELETE CASCADE ON UPDATE CASCADE)
Portanto, de forma ampla, quando referências FOREIGN KEY são definidas, é importante garantir que a tabela que está sendo referenciada tenha dados antes de ser referenciada.
Ações de integridade referencial
Vamos primeiro tentar entender o que exatamente é Integridade Referencial.
A integridade referencial ajuda a manter os dados em um estado limpo e consistente, onde há tabelas relacionadas umas às outras com um relacionamento FOREIGN KEY.
Simplificando, Integridade Referencial se refere à Ação que esperamos do mecanismo de banco de dados realizar, quando um UPDATE ou DELETE ocorre na tabela referenciada que contém a CHAVE ESTRANGEIRA.
Por exemplo, em nosso exemplo Funcionário / Departamento, suponha que alteramos o ID do Departamento para uma determinada linha no DB. Então, todas as linhas de referência na tabela Employee seriam afetadas. Podemos definir diferentes tipos de cenários de integridade referencial que podem ser aplicados durante esses casos.
Observação: A integridade referencial é definida durante a configuração / declaração da FOREIGN KEY como parte dos comandos / seções ON DELETE e ON UPDATE.
Consulte um exemplo de consulta aqui (para o exemplo Funcionário / Departamento):
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL, departmentName VARCHAR(100)); CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT, CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE {ref-integrity-action} ON DELETE {ref integrity action});
Insira alguns dados nessas tabelas como abaixo:
INSERT INTO department VALUES (1, 'ENGINEERING'), (2,'ACCOUNTING'), (3, 'MARKETING'), (4, 'HR'), (5, 'LEGAL'); INSERT INTO EMPLOYEE VALUES (1, 'AMIT KUMAR', 'MUMBAI', 32,'1988-02-12',1), (2, 'RYAN HILMAN', 'SEATTLE',43, '1977-03-15',1), (3, 'KAVITA SINGH', 'DELHI', 42, '1978-02-18',4), (4, 'DAVID BECKHAM', 'LONDON', 40, '1980-07-13',3), (5, 'PRITI KUMARI', 'DELHI', 35, '1985-12-11',2), (6, 'FRANK BALDING', 'NEW YORK', 35, '1985-08-25',5)
Existem 4 ações de referência que são suportadas pelo MySQL. Vamos tentar entender cada um deles.
# 1) CASCADE
Esta é uma das ações de integridade referencial mais comumente usadas. Definir DELETE e UPDATE como CASCADE aplicaria as alterações feitas à tabela de referência na tabela de referência, ou seja, no exemplo de Funcionário / Departamento. Suponha que alguém exclua uma linha na tabela Department tendo que dizer department_name = ACCOUNTING, então todas as linhas na tabela Employee tendo department_id como a tabela Accounting também seriam excluídas.
Vamos entender isso com um exemplo:
SELECT * FROM employee;
eu ia | nome | Morada | idade | dob | deptId |
---|---|---|---|---|---|
1 | AMIT KUMAR | MUMBAI | 32 | 12/02/1988 | 1 |
dois | RYAN HILMAN | SEATTLE | 43 | 15/03/1977 | 1 |
3 | KAVITA SINGH | DÉLHI | 42 | 18/02/1978 | 4 |
4 | DAVID BECKHAM | LONDRES | 40 | 13/07/1980 | 3 |
5 | PRITI KUMARI | DÉLHI | 35 | 11/12/1985 | dois |
6 | FRANK BALDING | NOVA YORK | 35 | 25/08/1985 | 5 |
Exclua o registro da tabela Department, onde departmentName = ’ACCOUNTING’
DELETE from DEPARTMENT WHERE departmentName='ACCOUNTING';
Agora, como é uma ação referencial CASCADE, esperaríamos que todas as linhas que têm departmentID = 2 (que é para o departamento ‘CONTABILIDADE’) também sejam excluídas. Vamos fazer uma consulta SELECT na tabela Employee novamente.
SELECT * FROM employee;
eu ia | nome | Morada | idade | dob | deptId |
---|---|---|---|---|---|
1 | AMIT KUMAR | MUMBAI | 32 | 12/02/1988 | 1 |
dois | RYAN HILMAN | SEATTLE | 43 | 15/03/1977 | 1 |
3 | KAVITA SINGH | DÉLHI | 42 | 18/02/1978 | 4 |
4 | DAVID BECKHAM | LONDRES | 40 | 13/07/1980 | 3 |
6 | FRANK BALDING | NOVA YORK | 35 | 25/08/1985 | 5 |
Como você pode ver acima, devido à integridade referencial de CASCADE, as linhas na tabela Employee que se referiram à coluna excluída como FOREIGN KEY terão essas linhas excluídas.
# 2) RESTRIÇÃO / SEM AÇÃO
O modo RESTRICT ou NO ACTION não permitirá nenhuma operação UPDATE ou DELETE na tabela com colunas referenciadas como FOREIGN KEY em alguma tabela.
O modo NO ACTION pode ser aplicado simplesmente omitindo as cláusulas ON UPDATE e ON DELETE da declaração da tabela.
para que serve o c ++?
Vamos tentar o mesmo exemplo e, neste caso, simplesmente pular a ação de integridade referencial ON UPDATE e ON DELETE.
Agora, quando tentamos excluir qualquer entrada na tabela referenciada, obteríamos um erro, pois definimos a ação referencial para RESTRICT
DELETE FROM department WHERE departmentName='ACCOUNTING';
Você verá um erro parecido com o abaixo se tentar executar o comando DELETE acima.
Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`my_sql_foreign_key`.`employee`, CONSTRAINT `depIdFk` FOREIGN KEY (`deptId`) REFERENCES `department` (`departmentId`))
# 3) DEFINIR NULO
Com SET NULL, qualquer UPDATE ou DELETE na tabela referenciada faria com que um valor NULL fosse atualizado contra o valor da coluna que está marcado como FOREIGN KEY na tabela de referência.
Com esta ação de integridade referencial, a definição da tabela Employee passaria a ser a seguinte:
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT, CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON DELETE SET NULL);
Exclua uma linha na tabela referenciada conforme mostrado abaixo:
DELETE FROM department WHERE departmentName='ACCOUNTING';
Agora, neste caso, o valor referenciado na tabela Employee seria definido como NULL. Faça uma consulta SELECT na tabela Employee para ver os resultados.
SELECT * FROM employee;
eu ia | nome | Morada | idade | dob | deptId |
---|---|---|---|---|---|
1 | AMIT KUMAR | MUMBAI | 32 | 12/02/1988 | 1 |
dois | RYAN HILMAN | SEATTLE | 43 | 15/03/1977 | 1 |
3 | KAVITA SINGH | DÉLHI | 42 | 18/02/1978 | 4 |
4 | DAVID BECKHAM | LONDRES | 40 | 13/07/1980 | 3 |
5 | PRITI KUMARI | DÉLHI | 35 | 11/12/1985 | NULO |
6 | FRANK BALDING | NOVA YORK | 35 | 25/08/1985 | 5 |
# 4) DEFINIR PADRÃO
O modo SET DEFAULT, quando especificado, resultaria na substituição do valor padrão da coluna (conforme especificado durante a declaração da coluna), no caso de qualquer DELETES na tabela sendo referenciada.
Observação - Conforme Documentação MySQL , a opção SET DEFAULT é suportada pelo MySQL Parser, mas não por mecanismos de banco de dados como InnoDB. Isso pode ser suportado no futuro.
No entanto, para suportar tal comportamento, você pode considerar o uso de SET NULL e definir um gatilho na tabela que pode definir um valor padrão.
Adicionar restrição FOREIGN KEY usando a instrução ALTER TABLE
Muitas vezes pode acontecer de querermos adicionar uma restrição FOREIGN KEY a uma tabela existente que não a possui.
Suponha que no exemplo Employee and Department, criamos uma tabela de funcionários sem qualquer restrição FOREIGN KEY e posteriormente queremos introduzir a restrição. Isso pode ser feito usando o comando ALTER TABLE.
Vamos tentar entender isso com um exemplo.
Suponha que tenhamos uma tabela Employee com a definição abaixo para o comando CREATE.
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT);
Aqui, temos uma coluna deptId, mas nenhuma restrição FOREIGN KEY. Neste caso, mesmo sem ter uma tabela Departamento, podemos especificar quaisquer valores ao inserir os registros.
Agora, mais tarde, suponha que temos uma tabela Department separada e queremos vincular departmentId lá como FOREIGN KEY à tabela Employee.
ALTER TABLE employee ADD CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE;
E se esta tabela tiver dados existentes? Podemos alterar a tabela e adicionar a restrição FOREIGN KEY?
A resposta é sim - podemos com a condição de que os valores existentes na coluna que serão referenciados em outra tabela tenham esses valores existentes na própria tabela pai.
Crie uma tabela Employee sem a restrição FOREIGN KEY, adicione alguns dados e tente adicionar uma restrição FOREIGN KEY usando o comando ALTER.
CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT);
INSERT INTO EMPLOYEE VALUES (1, 'DARREN JOHNSON', 'CHICAGO', 32, '1988-02-12',1); INSERT INTO EMPLOYEE VALUES (2, 'ANITA SHERWIN', 'COLUMBIA', 32, '1988-02-12',10);
Crie uma tabela Department e adicione FOREIGN KEY contra o campo ‘deptId’ na tabela Employee, conforme mostrado abaixo:
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL DEFAULT 1000, departmentName VARCHAR(100));
Neste ponto, se tentarmos adicionar a restrição FOREIGN KEY,
ALTER TABLE employee ADD CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE;
Então obteremos um erro, pois a tabela Employee contém alguns dados, mas a restrição de integridade referencial não pode ser atendida, pois a tabela Department ainda não possui dados.
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`my_sql_foreign_key`.`#sql-63_87`, CONSTRAINT `depIdFk` FOREIGN KEY (`deptId`) REFERENCES `department` (`departmentId`) ON DELETE CASCADE ON UPDATE CASCADE)
Para ter a restrição FOREIGN KEY, primeiro precisamos adicionar dados à tabela Department. Vamos inserir os registros necessários na tabela Departamento.
INSERT INTO department VALUES (1, 'ENGINEERING'),(10,'ACCOUNTING');
Adicione a restrição FOREIGN KEY novamente executando a mesma instrução ALTER TABLE. Você notará que, desta vez, o comando foi bem-sucedido e a tabela Employee foi atualizada com sucesso para ter deptId como FOREIGN KEY da tabela Department.
Eliminando uma restrição FOREIGN KEY
Semelhante a adicionar uma restrição FOREIGN KEY, também é possível descartar / excluir uma restrição FOREIGN KEY existente de uma tabela.
Isso pode ser feito usando o comando ALTER TABLE.
Sintaxe:
ALTER TABLE {childTable} DROP FOREIGN KEY {foreign key constraint name};
Aqui, ‘childTable’ é o nome da tabela que tem a restrição FOREIGN KEY definida, enquanto o ‘nome da restrição de chave estrangeira’ é o nome / símbolo que foi usado para definir a FOREIGN KEY.
Vejamos um exemplo usando a tabela Funcionário / Departamento. Para eliminar uma restrição chamada ‘depIdFk’ da tabela Employee, use o comando abaixo:
ALTER TABLE employee DROP FOREIGN KEY depIdFk;
perguntas frequentes
P # 1) Como posso alterar as chaves estrangeiras no MySQL?
Responda: FOREGIN KEY pode ser adicionado / removido usando o comando ALTER TABLE.
Para alterar ou adicionar uma nova FOREIGN KEY, você pode usar o comando ALTER e definir a FOREIGN KEY e a coluna da tabela de referência que seria referenciada na tabela filha.
P # 2) Como definir várias chaves estrangeiras no MySQL?
Responda: Uma tabela no MySQL pode ter várias FOREIGN KEYS, que podem depender da mesma tabela pai ou de tabelas pai diferentes.
Vamos usar a tabela Employee / Department e adicionar FOREIGN KEY para o nome do departamento, bem como DepartmentId na tabela Employee.
Consulte as instruções CREATE de ambas as tabelas abaixo
CREATE TABLE department(departmentId INT PRIMARY KEY NOT NULL DEFAULT 1000, departmentName VARCHAR(100) UNIQUE NOT NULL); ----xxxxx------xxxxx------xxxxx-------xxxxx------xxxxx CREATE TABLE employee(id INT PRIMARY KEY NOT NULL, name VARCHAR(100), address VARCHAR(100), age INT, dob DATE, deptId INT, depName VARCHAR(100), CONSTRAINT depIdFk FOREIGN KEY (deptId) REFERENCES department(departmentId) ON UPDATE CASCADE ON DELETE CASCADE, CONSTRAINT depNameFk FOREIGN KEY (depName) REFERENCES department(departmentName) ON UPDATE CASCADE ON DELETE CASCADE);
P # 3) Como desabilitar as restrições de chave estrangeira no MySQL?
Responda: As restrições FOREIGN KEY geralmente são necessárias quando alguém está tentando truncar uma tabela existente que está sendo referenciada. Para fazer isso, você pode usar o comando abaixo:
SET FOREIGN_KEY_CHECKS=0;
Isso definiria uma variável de sessão e desabilitaria temporariamente FOREIGN_KEY_CHECKS. Após esta configuração, você pode prosseguir e executar exclusões / truncar, o que de outra forma não seria possível.
Mas certifique-se de que este é um privilégio de administrador e deve ser usado com cautela.
P # 4) Como encontro as referências de chave estrangeira para uma tabela no MySQL?
Responda: Para listar todas as restrições FOREIGN KEY que estão presentes, você pode usar a tabela ‘INNODB_FOREIGN_COLS’ em ‘INFORMATION_SCHEMA`.
Basta executar o comando abaixo para obter todas as declarações FOREIGN KEY existentes para uma determinada instância do servidor MySQL.
EU IA | FOR_COL_NAME | REF_COL_NAME | POS |
---|---|---|---|
my_sql_foreign_key / depIdFk | deptId | departamentoId | 1 |
P # 5) A coluna referenciada como FOREIGN KEY deve ser uma chave primária na tabela referenciada?
Responda: Por definição de CHAVE ESTRANGEIRA, seria necessário que a coluna que está sendo referenciada como CHAVE ESTRANGEIRA seja a CHAVE PRIMÁRIA da tabela onde está sendo referenciada.
No entanto, com as versões mais recentes do MySQL e com o mecanismo de banco de dados InnoDB, você também pode fazer referência a uma coluna que possui FOREIGN KEY que possui uma restrição UNIQUE e pode não ser necessariamente PRIMARY KEY.
Q # 6) FOREIGN KEY cria INDEX no MySQL?
Responda: Para as restrições de chave primária e única, o MySQL cria automaticamente um INDEX para tais colunas.
Como já sabemos que as referências FOREIGN KEY só podem ser aplicadas a colunas que são chaves primárias ou colunas que têm valores únicos, portanto, todas as colunas que são referidas como FOREIGN KEY têm um índice criado contra elas.
Para visualizar o índice em uma tabela, use o comando abaixo:
SHOW INDEX from {dbName.tableName};
Portanto, para nosso exemplo de Funcionário / Departamento, adicionamos deptId em Funcionário como CHAVE ESTRANGEIRA da tabela Departamento.
Vamos ver os índices criados nas tabelas Employee e Department.
USE my_sql_foreign_key; SHOW INDEX from employee;
Mesa | Non_unique | Key_name | Seq_in_index | Nome da coluna | Collation | Cardinalidade | Sub_part | Embalado | Nulo | Index_type |
---|---|---|---|---|---|---|---|---|---|---|
empregado | 0 | PRIMÁRIO | 1 | eu ia | PARA | 0 | NULO | NULO | BTREE | |
empregado | 1 | depIdFk | 1 | deptId | PARA | 0 | NULO | NULO | SIM | BTREE |
Você pode ver 2 índices - um é a chave primária para a tabela Employee e outro é para FOREIGN KEY depId que é referenciado na tabela Department.
SHOW INDEX from department;
Mesa | Non_unique | Key_name | Seq_in_index | Nome da coluna | Collation | Cardinalidade | Sub_part | Embalado | Nulo | Index_type |
---|---|---|---|---|---|---|---|---|---|---|
departamento | 0 | PRIMÁRIO | 1 | departamentoId | PARA | 0 | NULO | NULO | BTREE |
Aqui você pode ver que, para a tabela Department, temos apenas 1 índice para a chave primária (que é referenciado como FOREIGN KEY na tabela Employee).
P # 7) FOREIGN KEY pode ser NULL no MySQL?
Responda: Sim, é perfeitamente normal ter NULL para a coluna que possui uma dependência FOREIGN KEY em outra tabela. Isso também alude ao fato de que NULL não é um valor real, portanto, não é correspondido / comparado com os valores na tabela pai.
Conclusão
Neste tutorial, aprendemos sobre diferentes conceitos relacionados ao uso de FOREIGN KEYS em bancos de dados MySQL.
FOREIGN KEY facilita as atualizações e exclusões com as restrições apropriadas, mas às vezes ter muitos desses relacionamentos pode tornar todo o processo de Inserção e / ou Exclusão bastante complicado.
Aprendemos como criar FOREIGN KEYS e como podemos atualizar e remover uma FOREIGN KEY existente da tabela filho. Também aprendemos sobre diferentes ações de integridade referencial e como podemos alcançar diferentes comportamentos usando as diferentes opções disponíveis como CASCADE, NO ACTION, SET NULL, etc.
Leitura recomendada
- Tutorial de criação de tabela MySQL com exemplos
- MySQL Insert Into Table - Sintaxe e exemplos de instruções de inserção
- Tutorial de criação de visualização do MySQL com exemplos de código
- Funções MySQL CONCAT e GROUP_CONCAT com exemplos
- Tutorial de transação do MySQL com exemplos de programação
- MySQL UNION - Tutorial abrangente com exemplos de união
- Como fazer download do MySQL para Windows e Mac
- Diferença entre SQL Vs MySQL Vs SQL Server (com exemplos)