Tutoriais » SQL » Tratamento de chave estrangeira »
Definindo:
O conceito de chave estrangeira (CE ou FK) é importante no modelo relacional, pois permite estabelecer as ligações e implementar as regras de negócio através dos seus valores.
Os SGBDs mais novos permitem criar a regra de comportamento dos valores de uma CE.
No SGBD MySQL para um campo ser uma CE algumas condições devem ser atendidas, quais sejam:
O tipo de dado e tamanho da CE DEVEM SER IGUAIS aos da chave primária (CP ou PK) correspondente;
O campo que vai ser CE DEVE TER índice
A tabela onde está a CE DEVE SER do tipo INNODB
O comportamento da CE deve ser feito com relação as operações de alteração e deleção na tabela que tem a CP.
Vamos exemplificar.
Se tivermos as tabelas:
O campo Cod_Cliente na tabela Pedidos é uma CE, correspondendo ao campo Cod_Cliente da tabela Clientes.
Vamos pensar na operação de exclusão de Clientes.
O programa deve identificar o cliente (pegar o código), acessar a tabela dos Clientes e apagar a tupla com o código escolhido.
Ao excluir um cliente da tabela de clientes, como queremos que se comporte os valores da CE (campo Cod_Cliente na tabela Pedidos)?
Vamos pensar na operação de alteração de Clientes.
Ao alterar um cliente da tabela de clientes (trocando o código de um cliente), como queremos que se comporte os valores da CE?
Para estas duas operações o padrão SQL preve 4 ações que são implementadas no MySQL e também no PostgreSQL.
Estas 4 ações são as seguintes:
CASCADE
Repetir a operação dos registros de pedidos que tem a CE igual ao valores da CP.
Se o campo Cod_Cliente em Pedidos for uma CE definida com CASCADE para DELETE então ao Deletar a tupla de Clientes com Cod_Cliente='20' a operação acontece também em Pedidos.
É como se escrevessemos 'DELETE FROM Clientes WHERE Cod_Cliente='20' AND DELETE FROM Pedidos WHERE Cod_Cliente='20''.
SET NULL
Colocar o valor nulo nos registros de pedidos correspondentes aos registros que estão sendo atualizados em Clientes
Se o campo Cod_Cliente em pedidos for uma CE definida com SET NULL para DELETE, então ao Deletar uma tupla de Clientes com Cod_Cliente='20' a operação que vai acontecer em Pedidos é um UPDATE colocando NULL no campo Cod_Cliente onde o Cod_Cliente era o valor era '20'.
É como se escrevessemos DELETE FROM Clientes WHERE Cod_Cliente='20' AND UPDATE Pedidos SET Cod_Cliente=NULL WHERE Cod_Cliente='20'.
Neste caso o campo Cod_Cliente em Pedidos deve poder aceitar valores nulos.
NO ACTION
Nada fazer (manter os valores da CE).
Se o campo Cod_Cliente em pedidos for uma CE definida com NO ACTION para DELETE, então ao Deletar uma tupla de Clientes com Cod_Cliente='20' NADA vai acontecer em Pedidos.
É evidente que isso gera uma situação de INCONSISTÊNCIA de valor entre CP e CE, mas se a regra de uma organização ditar que isso deve acontecer... o DBA e o Banco devem estar preparados para implementar a regra.
RESTRICT
NÃO excluir os registros da tabela onde está a CP se existir algum registro onde está a CE com o valor da CP que estiver sendo excluída.
Se o campo Cod_Cliente em pedidos for uma CE definida com RESTRICT para DELETE, então ANTES de Deletar uma tupla de Clientes com Cod_Cliente='20' o SGBD verifica se existe uma tupla em Pedidos onde o Cod_Cliente='20' e SE EXISTIR NÃO DELETA A TUPLA DE Clientes.
O comando que permite declarar esta ligação e o comportamento da CE pode ser visto abaixo:
ALTER TABLE depto01 ADD FOREIGN KEY (id_funcionario_gerente)
REFERENCES funcionarios (id_funcionario)
ON DELETE CASCADE
ON UPDATE CASCADE;