Chave estrangeira
Content
Resumo
A tabela que contém a chave estrangeira é chamada de tabela filho e a tabela que contém a chave do candidato é chamada de tabela referenciada ou pai. Na modelagem e implementação relacionais do banco de dados, uma chave candidata é um conjunto de zero ou mais atributos, cujos valores são garantidos para serem únicos para cada tupla (linha) em uma relação. O valor ou combinação de valores de atributos -chave candidatos para qualquer tupla não pode ser duplicada para qualquer outra tupla nessa relação.
Como o objetivo da chave estrangeira é identificar uma linha específica da tabela referenciada, geralmente é necessário que a chave estrangeira seja igual à chave do candidato em alguma linha da tabela primária, ou então não tenha valor (o valor nulo) . Esta regra é chamada de restrição de integridade referencial entre as duas tabelas. Como as violações dessas restrições podem ser a fonte de muitos problemas de banco de dados, a maioria dos sistemas de gerenciamento de banco de dados fornece mecanismos para garantir que cada chave estrangeira não nula corresponda a uma linha da tabela referenciada.
Por exemplo, considere um banco de dados com duas tabelas: uma tabela de clientes que inclui todos os dados do cliente e uma tabela de pedidos que inclui todos os pedidos do cliente. Suponha que a empresa exija que cada pedido consulte um único cliente. Para refletir isso no banco de dados, uma coluna de chave estrangeira é adicionada à tabela de pedidos (por exemplo, CustomerID), que faz referência à chave primária do cliente (por exemplo, ID). Como a chave primária de uma tabela deve ser única e, como o CustomerID contém apenas valores a partir desse campo de chave primária, podemos assumir que, quando tiver um valor, o CustomerID identificará o cliente específico que colocou o pedido. No entanto, isso não pode mais ser assumido se a tabela de pedidos não estiver atualizada quando as linhas da tabela do cliente forem excluídas ou a coluna ID alterada, e trabalhar com essas tabelas pode se tornar mais difícil. Muitos bancos de dados do mundo real contornam esse problema 'inativando', em vez de excluir fisicamente as chaves estrangeiras da tabela mestre ou por programas de atualização complexos que modificam todas as referências a uma chave estrangeira quando é necessária uma alteração.
As chaves estrangeiras desempenham um papel essencial no design do banco de dados. Uma parte importante do design do banco de dados é garantir que as relações entre as entidades do mundo real sejam refletidas no banco de dados por referências, usando chaves estrangeiras para se referir de uma tabela para outra. Outra parte importante do design do banco de dados é a normalização do banco de dados, na qual as tabelas são quebradas e as chaves estrangeiras possibilitam que elas sejam reconstruídas.
Várias linhas na tabela de referência (ou filho) podem se referir à mesma linha na tabela referenciada (ou pai). Nesse caso, a relação entre as duas tabelas é chamada de uma a muitas relações entre a tabela de referência e a tabela referenciada.
Além disso, a tabela infantil e pai pode, de fato, ser a mesma tabela, ou seja, a chave estrangeira refere -se à mesma tabela. Essa chave estrangeira é conhecida em SQL: 2003 como uma chave estrangeira auto-referenciante ou recursiva. Nos sistemas de gerenciamento de banco de dados, isso geralmente é realizado vinculando uma primeira e segunda referência à mesma tabela.
Uma tabela pode ter várias chaves estrangeiras e cada chave estrangeira pode ter uma tabela pai diferente. Cada chave estrangeira é aplicada de forma independente pelo sistema de banco de dados. Portanto, as relações em cascata entre tabelas podem ser estabelecidas usando chaves estrangeiras.
Uma chave estrangeira é definida como um atributo ou conjunto de atributos em uma relação cujos valores correspondem a uma chave primária em outra relação. A sintaxe para adicionar essa restrição a uma tabela existente é definida no SQL: 2003, como mostrado abaixo. A omitir a lista de colunas na cláusula de referências implica que a chave estrangeira deve fazer referência à chave primária da tabela referenciada. As chaves estrangeiras podem ser definidas como parte da instrução CREATE TABLE SQL.
Se a chave estrangeira for apenas uma única coluna, a coluna pode ser marcada como tal usando a seguinte sintaxe:
As chaves estrangeiras podem ser definidas com uma declaração de procedimento armazenado.
Ações referenciais
Como o sistema de gerenciamento de banco de dados aplica restrições referenciais, ele deve garantir a integridade dos dados se linhas em uma tabela referenciada forem excluídas (ou atualizadas). Se ainda existem linhas dependentes nas tabelas de referência, essas referências devem ser consideradas. SQL: 2003 Especifica 5 ações referenciais diferentes que devem ocorrer em tais ocorrências:
CASCADERESTRICTNO ACTIONSET NULLSET DEFAULTCASCATA
Sempre que linhas na tabela pai (referenciado) são excluídas (ou atualizadas), as respectivas linhas da tabela (referência) com uma coluna de chave estrangeira correspondentes também serão excluídas (ou atualizadas). Isso é chamado de exclusão de cascata (ou atualização).
RESTRINGIR
Um valor não pode ser atualizado ou excluído quando uma linha existe em uma tabela de referência ou filho que referencia o valor na tabela referenciada.
Da mesma forma, uma linha não pode ser excluída desde que haja uma referência a ele de uma tabela de referência ou criança.
Para entender melhor (e cascata), pode ser útil notar a seguinte diferença, que pode não ser imediatamente clara. A ação referencial em cascata modifica o "comportamento" da própria tabela (criança) onde a palavra cascata é usada. Por exemplo, na Excluir Cascade diz efetivamente "quando a linha referenciada é excluída da outra tabela (tabela mestre), depois exclua também de mim". No entanto, a ação referencial restringe modifica o "comportamento" da tabela mestre, não a tabela infantil, embora a palavra restrinja na tabela infantil e não na tabela principal! Portanto, ao excluir restringir efetivamente: "Quando alguém tenta excluir a linha da outra tabela (tabela mestre), impede a exclusão dessa outra tabela (e, claro, também não exclua de mim, mas esse não é o ponto principal aqui)."
O restrito não é suportado pela Microsoft SQL 2012 e anteriormente.
Nenhuma ação
Nenhuma ação e restrição são muito iguais. A principal diferença entre nenhuma ação e restrição é que, sem ação, a verificação de integridade referencial é feita após tentar alterar a tabela. A restrição faz a verificação antes de tentar executar a instrução Atualizar ou excluir. Ambas as ações referenciais agem da mesma forma se a verificação de integridade referencial falhar: a instrução Atualização ou exclusão resultará em um erro.
Em outras palavras, quando uma declaração de atualização ou exclusão é executada na tabela referenciada usando a ação referencial sem ação, o DBMS verifica no final da execução da declaração de que nenhum dos relacionamentos referenciais é violado. Isso é diferente do restrito, que assume desde o início que a operação violará a restrição. Usando nenhuma ação, os gatilhos ou a semântica da própria declaração podem produzir um estado final em que nenhum relacionamento chave estrangeiro é violado quando a restrição é finalmente verificada, permitindo que a declaração seja concluída com sucesso.
Defina nulo, defina padrão
Em geral, a ação tomada pelo DBMS para definir NULL ou Set Inadim é a mesma para excluir ou atualizar: o valor dos atributos de referência afetado é alterado para NULL para definir nulo e para o valor padrão especificado para definir padrão padrão .
Gatilhos
Ações referenciais são geralmente implementadas como gatilhos implícitos (ou seja, gatilhos com nomes gerados pelo sistema, geralmente ocultos) como tal, eles estão sujeitos às mesmas limitações que os gatilhos definidos pelo usuário, e sua ordem de execução em relação a outros gatilhos pode precisar ser considerado; Em alguns casos, pode ser necessário substituir a ação referencial por seu gatilho equivalente definido pelo usuário para garantir a ordem de execução adequada ou para contornar as limitações da tabela de mutações.
Outra limitação importante aparece com o isolamento da transação: suas alterações em uma linha podem não ser capazes de em cascata totalmente porque a linha é referenciada pelos dados que sua transação não pode "ver" e, portanto, não pode cascata. Um exemplo: enquanto sua transação está tentando renumerar uma conta do cliente, uma transação simultânea está tentando criar uma nova fatura para o mesmo cliente; Embora uma regra em cascata possa corrigir todas as linhas de fatura que sua transação pode ver para mantê -las consistentes com a linha do cliente renumerada, ela não chegará a outra transação para corrigir os dados lá; Como o banco de dados não pode garantir dados consistentes quando as duas transações se comprometem, uma delas será forçada a reverter (geralmente com base no primeiro a chegar, primeiro a ser servido.)
Exemplo
Como primeiro exemplo para ilustrar as chaves estrangeiras, suponha que um banco de dados de contas tenha uma tabela com faturas e cada fatura esteja associada a um fornecedor específico. Detalhes do fornecedor (como nome e endereço) são mantidos em uma tabela separada; Cada fornecedor recebe um 'número do fornecedor' para identificá -lo. Cada registro da fatura possui um atributo que contém o número do fornecedor para essa fatura. Em seguida, o 'número do fornecedor' é a chave primária na tabela de fornecedores. A chave estrangeira na tabela de faturas aponta para essa chave primária. O esquema relacional é o seguinte. As chaves primárias são marcadas em negrito e as chaves estrangeiras são marcadas em itálico.
Supplier (SupplierNumber, Name, Address) Invoice (InvoiceNumber, Text, SupplierNumber)A declaração de linguagem de definição de dados correspondente é a seguinte.