Qual é a vantagem das chaves estrangeiras em SQL?

O que é uma chave estrangeira e por que ela é tão importante em bancos de dados relacionais? Conheça todos os detalhes sobre chaves estrangeiras neste artigo.

Uma das principais características das bases de dados relacionais é a capacidade de vincular dados armazenados em diferentes tabelas. Estes links, chamados de referências, funcionam essencialmente como conexões entre as tabelas. Eles são criados usando a restrição FOREIGN KEY nas colunas das tabelas.

Neste artigo, vamos aprender o que a restrição FOREIGN KEY faz em SQL. Explicarei como defini-la usando a declaração CREATE TABLE e passaremos por alguns exemplos. A seguir, vamos falar sobre seus benefícios e características. Especificamente:

• Aprenderemos sobre a relação entre a tabela primária (que fornece sua(s) coluna(s) chave primária(s) à tabela estrangeira) e a tabela estrangeira (que usa a(s) coluna(s) fornecida(s) pela tabela primária como sua chave estrangeira).
• Vamos falar sobre o que acontece quando os valores da coluna da tabela primária são excluídos ou alterados. Há várias opções oferecidas pela restrição FOREIGN KEY em tais situações - vou esclarecê-las, incluindo alguns exemplos.
• No final, discutiremos as opções de cardinalidade que podem ser implementadas com a FOREIGN KEY

Vamos começar.

O que é uma restrição de chave estrangeira em SQL?

Para entender o conceito da restrição FOREIGN KEY em SQL, você pode pensar nela como um link de referência entre tabelas conhecidas como tabelas primárias (ou de pais) e tabelas estrangeiras (ou de filhos). A tabela estrangeira faz referência a uma ou mais colunas (a chave primária, que pode ser uma ou mais colunas) na tabela primária; é assim que o link é criado. Se você precisar atualizar seus conhecimentos sobre chaves primárias em SQL, recomendo a leitura do artigo O que é uma chave primária?

Um exemplo em Fotos

Acredito que alguns auxílios visuais podem ser úteis aqui. Vamos dar uma olhada nas imagens abaixo.

Aqui, nós temos o Airplane mesa (a mesa principal) e o Flight mesa (a mesa estrangeira). A coluna AirplaneId, que é uma coluna chave primária para a tabela Airplane, é usada como coluna chave estrangeira na Flight mesa. É assim que se cria a relação entre estas tabelas - a coluna AirplaneId do Flight A tabela define qual avião é utilizado para cada vôo.

Favor observar que a coluna AirplaneId da tabela estrangeira Flight não precisa conter todos os valores armazenados no Airplane.AirplaneId. Pode conter um subconjunto desses valores:

Os valores contidos na coluna Flight.AirplaneId referem-se diretamente aos valores contidos na coluna Airplane.AirplaneId. Portanto, qualquer ação de atualização ou exclusão nas linhas da tabela primária Airplane deve ser refletida adequadamente pela tabela estrangeira Flight. A restrição FOREIGN KEY oferece várias opções para implementar essas atualizações ou exclusões, que serão discutidas posteriormente.

E mais uma coisa importante. O exemplo acima mostra uma relação entre a Airplane e Flight tabelas usando apenas uma coluna. Também é possível utilizar várias colunas para criar tais relações entre as tabelas.

Aqui, em vez de usar a coluna AirplaneId para a restrição FOREIGN KEY, decidimos usar as colunas AirplaneBrand e AirplaneModel, pois elas também identificam de forma única cada linha da tabela. Airplane (assumindo que nossa companhia aérea possua apenas um avião de cada par-modelo de marca listado na tabela).

Você pode notar que também podemos colocar todos os dados em uma única tabela chamada AirplaneFlight. Isso é verdade; entretanto, não seríamos capazes de segregar os dados em diferentes categorias dentro de uma tabela. Esta é a razão pela qual as chaves estrangeiras são uma parte tão crucial do projeto do banco de dados. Elas nos permitem colocar os dados relacionados em várias tabelas e depois ligá-las entre si para manter sua integridade.

Como definir a restrição da chave estrangeira em SQL

Agora queremos trazer nosso projeto para o banco de dados. Podemos fazê-lo usando a declaração CREATE TABLE e definindo a restrição FOREIGN KEY dentro dela.

Vamos ver como fica a declaração CREATE TABLE se definirmos a restrição FOREIGN KEY com base no exemplo acima, que usa a coluna AirplaneId.

Primeiro, devemos criar a tabela primária e definir sua coluna chave primária.

CREATE TABLE Airplane (
AirplaneId VARCHAR(10) NOT NULL,
AirplaneBrand VARCHAR(30) NOT NULL,
AirplaneModel VARCHAR(30) NOT NULL,
CONSTRAINT PK_AirplaneId PRIMARY KEY (AirplaneId)
);

Para rever a restrição PRIMARY KEY, verifique o que é uma chave primária em SQL?

Em seguida, criamos a tabela estrangeira:

CREATE TABLE Flight (
FlightId VARCHAR(10) NOT NULL,
AirplaneId VARCHAR(10) NOT NULL,
PilotId INTEGER NOT NULL,
CONSTRAINT PK_FlightId PRIMARY KEY (FlightId),
CONSTRAINT FK_AirplaneId FOREIGN KEY (AirplaneId)
REFERENCES Airplane(AirplaneId)
);

Aqui, além da restrição PRIMARY KEY, definimos a restrição FOREIGN KEY. Demos-lhe o nome FK_AirplaneId. Dentro de parênteses, definimos a coluna da Flight tabela que implementa a restrição FOREIGN KEY. Após a palavra-chave REFERENCES, segue-se o nome da tabela primária e uma coluna.

É assim que podemos mover nosso conceito de projeto de banco de dados para um banco de dados funcional.

Pode acontecer que desejemos adicionar a restrição FOREIGN KEY à tabela de Flight tabela após a criação da tabela. Para fazer isso, usamos a declaração ALTER TABLE:

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId);

Talvez também queiramos abandonar uma restrição FOREIGN KEY. Suponha que queiramos mudar a restrição FOREIGN KEY definida acima para sua versão multicoluna. Primeiro devemos abandoná-la e depois criar a nova chave:

ALTER TABLE Flight DROP FOREIGN KEY FK_AirplaneId;
ALTER TABLE Flight ADD FOREIGN KEY FK_Airplane (AirplaneBrand, AirplaneModel)
REFERENCES Airplane(AirplaneBrand, AirplaneModel);

Agora, a tabela<forte>Flight usa a restrição de multicoluna FOREIGN KEY .

Se você acha que precisa de mais prática com a criação de tabelas de banco de dados, confira nosso curso sobre As Bases da Criação de Tabelas em SQL.

Quais são os benefícios das chaves estrangeiras?

Agora que aprendemos todos os fundamentos da restrição FOREIGN KEY, suas características e as possibilidades que ela oferece para a funcionalidade do banco de dados, podemos mergulhar mais profundamente em seus benefícios.

Como já mencionei, ao implementar uma restrição FOREIGN KEY, podemos distinguir entre a tabela primária e a tabela estrangeira.

A tabela primária fornece uma coluna, ou um conjunto de colunas, que é utilizada pela tabela estrangeira. Em outras palavras, a coluna da tabela estrangeira (ou conjunto de colunas) faz referência a uma coluna (ou conjunto de colunas) na tabela primária.

Portanto, o primeiro benefício da restrição FOREIGN KEY é que ela garante a existência da linha referenciada na tabela primária. Se a linha referenciada não estiver na tabela primária, ela não poderá estar presente na tabela estrangeira.

Outro benefício da restrição FOREIGN KEY (FK) é que ela garante a correção da referência mesmo quando os valores da tabela primária são modificados ou excluídos. Há uma variedade de ações que indicam o que fazer na atualização ou exclusão de uma linha da chave primária (PK). Vamos ver o que são essas ações.

Quando uma linha de chave primária é apagada

Há uma série de opções oferecidas pela restrição FOREIGN KEY em relação ao que fazer com a exclusão da(s) linha(s) de chave primária(s) da tabela primária.

Por padrão, quando nenhuma ação é especificada para a restrição FOREIGN KEY, não é possível excluir a linha relevante na tabela primária (pai) se houver linhas que façam referência a ela na tabela estrangeira (filho).

Você pode especificar a ação a ser tomada na eliminação da(s) linha(s) PK da tabela primária, escolhendo uma das seguintes opções:

• ON DELETE CASCADE
• ON DELETE RESTRICT
• ON DELETE NO ACTION
• ON DELETE SET NULL
• ON DELETE SET DEFAULT

Vamos analisar cada uma delas. Primeiro, analisaremos a sintaxe que todas estas opções compartilham.

SOBRE Sintaxe DELETE

A declaração da opção ON DELETE segue diretamente após a declaração da restrição FOREIGN KEY.

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId)
ON DELETE ;

Agora vamos analisar todas as opções.

SOBRE EXCLUIR CASCATA

A opção ON DELETE CASCADE garante que quando uma linha é removida da tabela primária PK, a linha da tabela estrangeira que a referencia também é removida.

Vamos ver isso em ação usando nossa Airplane e Flight tabelas. Em primeiro lugar, temos as duas tabelas relacionadas:

Removemos uma fileira da tabela primária:

E agora, a linha que faz referência à linha eliminada é eliminada da tabela estrangeira.

Para implementar a opção ON DELETE CASCADE, adicione-a após a declaração de restrição em FOREIGN KEY:

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId)
ON DELETE CASCADE;

Você deve pensar em implementar a opção ON DELETE CASCADE com a restrição FOREIGN KEY quando quiser:

• A capacidade de remover linhas da tabela primária sem qualquer restrição.
• Eliminação automática das linhas na tabela estrangeira quando a linha de referência da tabela primária for eliminada.

NA RESTRIÇÃO DE EXCLUSÃO

A opção ON DELETE RESTRICT não permite a exclusão de linhas de chave primária na tabela primária. Quando uma ação desse tipo é tentada, um erro é retornado.

Isto é útil para evitar qualquer mudança indesejada nas tabelas primária e estrangeira.

SOBRE EXCLUIR NENHUMA AÇÃO

A opção ON DELETE NO ACTION é semelhante à opção ON DELETE RESTRICT; também impede a exclusão das linhas da tabela primária e lança um erro se tal ação for tentada.

A diferença entre ON DELETE NO ACTION e ON DELETE RESTRICT é que em alguns bancos de dados ON DELETE NO ACTION pode ser adiado, ou seja, pode ser adiado para depois da transação para que o eventual erro seja lançado após a conclusão da transação - ou seja, no curso da transação o valor da chave estrangeira pode ser corrigido, caso contrário o erro é lançado.

NO APAGAR SET NULL

A opção ON DELETE SET NULL faz exatamente o que diz. Quando uma linha é excluída da tabela primária, os valores das colunas que a referenciam na tabela estrangeira são definidos para NULL.

Vejamos um exemplo abaixo. Aqui estão as duas tabelas novamente.

Retiramos uma linha da tabela primária.

E agora, o valor da coluna AirplaneId da linha que faz referência à linha eliminada é definido como NULL na tabela estrangeira.

A opção ON DELETE SET NULL pode ser usada quando você quiser permitir a exclusão de linha na tabela primária, mas não quer que ela seja propagada para a tabela estrangeira. Há uma pegada da exclusão na tabela estrangeira (o valor marcado como NULL).

NA EXCLUSÃO DEFINIR PADRÃO

A opção ON DELETE SET DEFAULT é um pouco semelhante à opção ON DELETE SET NULL. Aqui, após a exclusão da linha na tabela primária, definimos o valor da coluna FK da tabela estrangeira para seu valor padrão ao invés de NULL.

Favor observar que um valor padrão deve ser especificado para a(s) coluna(s) chave estrangeira(s).

Vamos especificar um valor padrão para a coluna AirplaneId do Flight mesa:

ALTER TABLE Flight
ALTER AirplaneId
SET DEFAULT 'ABA340';

Agora podemos usar a opção ON DELETE SET DEFAULT. Após a exclusão da linha da tabela primária, acontecerá o seguinte:

Você deve usar a opção ON DELETE SET DEFAULT quando quiser permitir a exclusão da linha na tabela primária. O grau de controle aqui é que você pode escolher qual valor é inserido na tabela estrangeira na exclusão da linha na tabela primária.

Quando uma linha da chave primária é atualizada

As ações oferecidas pela restrição FOREIGN KEY sobre a atualização realizada na tabela primária são similares às ações sobre exclusão. Assim é a sintaxe.

SOBRE A Sintaxe da Atualização

A declaração da opção ON UPDATE segue diretamente após a declaração da restrição FOREIGN KEY.

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId)
ON UPDATE ;

Por padrão, quando nenhuma ação é especificada para a restrição FOREIGN KEY, não é possível modificar as linhas da tabela primária (pai) se houver linhas que façam referência a ela na tabela estrangeira (filho).

Você pode definir o que acontece na atualização da(s) linha(s) da tabela primária, escolhendo uma das seguintes opções:

• ON UPDATE CASCADE
• ON UPDATE RESTRICT
• ON UPDATE NO ACTION
• ON UPDATE SET NULL
• ON UPDATE SET DEFAULT

Vamos analisar cada uma delas.

EM CASCATA DE ATUALIZAÇÃO

A opção ON UPDATE CASCADE permite modificar os valores na(s) coluna(s) PK da tabela primária. Estas mudanças são então propagadas para a tabela estrangeira.

Vamos ver isso em ação usando nossa Airplane e Flight tabelas:

Modificamos os valores na última linha da tabela primária.

E agora, a linha que faz referência a ela também é atualizada.

Para implementar a opção ON UPDATE CASCADE, adicione-a após a declaração de restrição FOREIGN KEY:

ALTER TABLE Flight ADD FOREIGN KEY FK_AirplaneId (AirplaneId)
REFERENCES Airplane(AirplaneId)
ON UPDATE CASCADE;

Esta opção permite a modificação da(s) coluna(s) chave primária(s) da tabela primária. Qualquer modificação é então propagada para a(s) chave(s) estrangeira(s).

SOBRE A RESTRIÇÃO DE ATUALIZAÇÃO

Se você quiser evitar qualquer modificação na tabela primária PK, use a opção ON UPDATE RESTRICT.

Em qualquer tentativa de modificar os valores PK da tabela primária, um erro é retornado.

NA ATUALIZAÇÃO NENHUMA AÇÃO

A opção ON UPDATE NO ACTION é semelhante à opção ON UPDATE RESTRICT. Não são permitidas mudanças no PK da mesa principal.

A diferença é que alguns bancos de dados permitem que as verificações das ações sejam adiadas para depois da transação com ON UPDATE NO ACTION. Se os valores PK da tabela primária forem alterados quando a transação for concluída, um erro é lançado.

NO CONJUNTO DE ATUALIZAÇÃO NULO

A opção ON UPDATE SET NULL permite modificações no PK da tabela primária. Em tais atualizações, os valores na tabela estrangeira FK estão definidos para NULL.

Vejamos um exemplo abaixo, começando com as duas tabelas:

Modificamos os valores na última linha da tabela primária.

E agora, a linha que faz referência a ela está configurada para NULL.

EM UPDATE SET DEFAULT

A opção ON UPDATE SET DEFAULT permite modificações no PK da tabela primária. Como resultado, os valores na chave estrangeira são definidos para seu valor padrão. Você só pode usar esta opção se um valor padrão for declarado para a(s) coluna(s) da chave estrangeira.

Vamos especificar o valor padrão para a coluna AirplaneId do Flight mesa:

ALTER TABLE Flight
ALTER AirplaneId
SET DEFAULT 'ABA340';

Agora, podemos usar a opção ON UPDATE SET DEFAULT. Após a atualização para a linha da tabela primária, acontecerá o seguinte:

Chave Estrangeira de Restrição de Cardinalidade

Outra característica oferecida pela restrição FOREIGN KEY é sua cardinalidade. Ao estabelecer a cardinalidade, podemos decidir como a(s) coluna(s) de chave estrangeira(s) se relaciona(m) com a(s) coluna(s) de chave primária(s). As relações podem ser:

• Um-a-um (um valor PK pode aparecer exatamente em uma fila FK).
• Many-to-one (um valor de linha PK pode aparecer em uma ou várias fileiras FK).
• Many-to-many (muitos valores de linha PK podem aparecer em muitas linhas FK).

Para implementar a relação um-para-um, devemos declarar a(s) coluna(s) chave(s) estrangeira(s) como UNIQUE. Neste caso, a relação é de fato (um ou zero) para um, mas garante que haja no máximo uma linha na tabela estrangeira que se relacione com uma linha específica da tabela primária.

Por outro lado, se a(s) coluna(s) de chave estrangeira da tabela estrangeira não forem declaradas como UNIQUE, então a relação entre a(s) coluna(s) de chave estrangeira e a(s) coluna(s) de chave primária é de muitas linhas da tabela estrangeira - muitas linhas da tabela estrangeira podem se relacionar a uma única linha da tabela primária.

A relação entre muitos e muitos é implementada usando uma tabela separada que armazena cada par de fileiras relacionadas.

Note que quando criamos esta relação entre muitos e muitos, a ligação direta entre o Airplane e Flight As tabelas se transformam em uma tabela separada que armazena todas as relações em AirplaneId-FlightId pares.

O AirplaneFlight tabela tem uma chave estrangeira (AirplaneId) que se refere à coluna AirplaneId do Airplane mesa. Ela também tem uma chave estrangeira (FlightId) que se refere à coluna FlightId da Flight mesa. A Airplane e Flight As tabelas estão agora indiretamente ligadas umas às outras.

Deseja saber mais sobre chaves estrangeiras em SQL?

A restrição FOREIGN KEY é crucial para o projeto de banco de dados relacional. Ela nos permite vincular os dados de acordo com nossas necessidades. Como cria algumas dependências entre as colunas das tabelas primárias e estrangeiras, também nos permite decidir o que fazer ON UPDATE e ON DELETE ações realizadas nas linhas da tabela primária.

Ao utilizar a restrição FOREIGN KEY, garantimos a integridade, correção e compactação dos dados. Os valores usados pela tabela estrangeira devem existir na tabela primária. Também podemos impor regras sobre o tipo de relacionamento, ou seja, um-para-um, muitos-para-um, ou muitos-para-muitos.

Se você quiser aprender ainda mais sobre chaves estrangeiras, consulte nosso artigo sobre O que é uma chave estrangeira em SQL?

Para obter mais informações sobre a estrutura e o projeto do banco de dados, recomendo nossa faixa em Criação de Estrutura de Banco de Dados, que inclui todos os elementos essenciais. Você pode ler sobre esta trilha aqui antes de saltar para dentro dela.