Esta página descreve as chaves estrangeiras no Spanner e como usá-las para impor a integridade de referência no seu banco de dados.
As chaves externas definem relações entre tabelas. O Spanner garante que a integridade de dados desses relacionamentos seja mantida.
Imagine que você é o desenvolvedor líder de uma empresa de e-commerce. Você está projetando um banco de dados para processar pedidos dos clientes. O banco de dados precisa armazenar informações sobre cada pedido, cliente e produto. A figura 1 ilustra a estrutura de banco de dados básica do aplicativo.
Figura 1. Diagrama de um banco de dados de processamento de pedidos
Você define uma tabela Customers
para armazenar informações do cliente, uma tabela Orders
para acompanhar todos os pedidos e uma tabela Products
para armazenar informações sobre cada produto.
A Figura 1 também mostra links entre as tabelas que mapeiam aos seguintes relacionamentos reais:
Um cliente faz um pedido.
Um produto é solicitado.
Você decide que seu banco de dados precisa aplicar as regras a seguir para garantir que os pedidos no seu sistema sejam válidos.
Não é possível criar um pedido para um cliente que não existe.
Um cliente não pode fazer um pedido de um produto que você não tem disponível.
Quando você aplica essas regras, ou restrições, está mantendo a integridade referencial dos seus dados. Quando um banco de dados mantém a integridade referencial, todas as tentativas de adicionar dados inválidos, o que resultaria em links ou referências inválidos entre os dados, falham. A integridade referencial evita erros do usuário. O Spanner aplica a integridade referencial por meio de chaves estrangeiras.
Aplicar integridade referencial com chaves estrangeiras
O exemplo a seguir examina o processamento de pedidos novamente, com mais detalhes adicionados ao design, conforme mostrado na Figura 2.
Figura 2. Diagrama de um esquema de banco de dados com chaves estrangeiras
O design agora mostra nomes e tipos de colunas em cada tabela. A tabela Orders
também define dois relacionamentos de chave estrangeira. FK_CustomerOrder
garante que todas
as linhas em Orders
tenham um CustomerID
válido. A chave estrangeira FK_ProductOrder
garante que todos os valores ProductID
na tabela Orders
sejam válidos. A
tabela a seguir mapeia essas restrições de volta às regras reais que você
quer aplicar.
Nome da chave estrangeira | Restrição | Descrição real |
---|---|---|
FK_CustomerOrder | Garante que todas as linhas em Orders tenham um
CustomerID válido |
Um cliente válido faz um pedido |
FK_ProductOrder | Garante que todas as linhas em Orders tenham um
ProductID válido. |
Um produto válido foi solicitado |
O Spanner falha em qualquer transação que tente inserir ou atualizar uma
linha na tabela Orders
que tenha um CustomerID
ou ProductID
não encontrado nas
tabelas Customers
e Products
. Ele também falha em transações que tentam
atualizar ou excluir linhas nas tabelas Customers
e Products
que invalidariam
os IDs na tabela Orders
. Para mais detalhes sobre como
o Spanner valida restrições, consulte a seção Validação de restrição
de transação.
Características da chave estrangeira
Confira a seguir uma lista de características de chaves estrangeiras no Spanner.
A tabela que define a chave externa é a tabela de referência, e as colunas de chave externa são as colunas de referência.
A chave estrangeira faz referência às colunas referenciadas da tabela referenciada.
Como no exemplo, você pode nomear cada restrição chave externa. Se você não especificar um nome, o Spanner vai gerar um para você. É possível consultar o nome gerado no
INFORMATION_SCHEMA
do Spanner. Os nomes das restrições têm como escopo o esquema, além dos nomes das tabelas e índices. Eles precisam ser exclusivos dentro do esquema.O número de colunas de referência e referenciadas precisa ser o mesmo. A ordem é importante. A primeira coluna de referência se refere à primeira coluna referenciada, a segunda à segunda etc.
As colunas de referência e a contraparte referenciada precisam ser do mesmo tipo. É necessário indexar as colunas.
Não é possível criar chaves estrangeiras em colunas com a opção
allow_commit_timestamp=true
.As colunas da matriz não são compatíveis.
Colunas JSON não são compatíveis.
Uma chave externa pode fazer referência a colunas da mesma tabela (uma chave externa de referência própria). Um exemplo é uma tabela
Employee
com uma colunaManagerId
que faz referência à colunaEmployeeId
da tabela.As chaves estrangeiras também podem formar relações circulares entre tabelas, em que duas tabelas fazem referência umas às outras, de maneira direta ou indireta. A tabela referenciada precisa existir antes da criação de uma chave externa. Isso significa que pelo menos uma das chaves externas precisa ser adicionada usando a instrução
ALTER TABLE
.As chaves referenciadas precisam ser exclusivas. O Spanner vai usar o
PRIMARY KEY
da tabela referenciada se as colunas referenciadas da chave externa corresponderem às colunas da chave primária da tabela referenciada. Se o Spanner não puder usar a chave primária da tabela referenciada, ele criará umUNIQUE NULL_FILTERED INDEX
sobre as colunas referenciadas.O Spanner também pode usar a chave primária da tabela de referência, embora isso seja menos comum. Caso contrário, o Spanner cria um
NULL_FILTERED INDEX
nas colunas de referência.As chaves estrangeiras não usam índices secundários criados por você. Em vez disso, elas criam os próprios índices de backup. Os índices de backup podem ser usados em avaliações de consulta, incluindo em diretivas
force_index
explícitas. É possível consultar os nomes dos índices de backup doINFORMATION_SCHEMA
do Spanner. Para mais informações, consulte Índices de backup.
Definir chaves estrangeiras
Você cria e remove chaves estrangeiras do banco de dados do Spanner
usando DDL. Você adiciona chaves estrangeiras a uma nova tabela com a instrução
CREATE TABLE
. Da mesma forma, você pode adicionar ou remover uma chave externa de uma tabela atual com a instrução ALTER TABLE
. Veja a seguir um exemplo de como criar uma nova tabela com uma chave estrangeira.
GoogleSQL
CREATE TABLE Orders (
OrderID INT64 NOT NULL,
CustomerID INT64 NOT NULL,
Quantity INT64 NOT NULL,
ProductID INT64 NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
) PRIMARY KEY (OrderID);
PostgreSQL
CREATE TABLE Orders (
OrderID BIGINT NOT NULL,
CustomerID BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
ProductID BIGINT NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
PRIMARY KEY (OrderID)
);
Para mais exemplos de como criar e gerenciar chaves estrangeiras, consulte Criar e gerenciar chave externa estrangeiras.
Ações de chave estrangeira
As ações de chave estrangeira controlam o que acontece com a coluna restrita quando a
coluna referenciada é excluída ou atualizada. O Spanner oferece suporte ao
uso da ação ON DELETE CASCADE
. Com a ação ON DELETE CASCADE
de chave externa, quando você exclui uma linha que contém uma chave externa referenciada, todas as linhas
que fazem referência a essa chave também são excluídas na mesma transação.
É possível adicionar uma chave externa com uma ação ao criar o banco de dados usando
DDL. Use a instrução CREATE TABLE
para adicionar chaves estrangeiras com uma ação a
uma nova tabela. Da mesma forma, é possível usar a instrução ALTER TABLE
para adicionar uma ação chave externa a uma tabela ou remover uma ação chave externa. Confira a seguir um exemplo de como criar uma nova tabela com uma ação chave externa.
GoogleSQL
CREATE TABLE Customers (
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);
CREATE TABLE ShoppingCarts (
CartId INT64 NOT NULL,
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
CONSTRAINT FKShoppingCartsCustomers FOREIGN KEY(CustomerId, CustomerName)
REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE,
) PRIMARY KEY(CartId);
PostgreSQL
CREATE TABLE Customers (
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CustomerId)
);
CREATE TABLE ShoppingCarts (
CartId bigint NOT NULL,
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CartId),
CONSTRAINT fkshoppingcartscustomers FOREIGN KEY (CustomerId, CustomerName)
REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE
);
Confira a seguir uma lista de características de ações de chave externa no Spanner.
As ações de chaves estrangeiras são
ON DELETE CASCADE
ouON DELETE NO ACTION
.É possível consultar o
INFORMATION_SCHEMA
para encontrar restrições chave externa que têm uma ação.Não é possível adicionar uma ação de chave externa a uma restrição de chave externa existente. Você precisa adicionar uma nova restrição chave externa com uma ação.
Alterações de esquema de longa duração
Adicionar uma chave estrangeira a uma tabela atual ou criar uma nova tabela com uma chave estrangeira pode levar a operações de longa duração. No caso de uma nova tabela, ela não pode ser gravada até que a operação de longa duração seja concluída.
Para uma nova tabela com uma chave externa, o Spanner precisa preencher os índices referenciados conforme necessário para cada chave externa.
Para uma tabela com uma chave externa, o Spanner precisa preencher os índices de referência e referenciados conforme necessário. Além disso, o Spanner valida os dados atuais na tabela para garantir que ela obedeça à restrição de integridade de referência da chave externa. A mudança de esquema falhará se houver dados inválidos.
Não é possível adicionar uma ação de chave externa a uma restrição. Faça o seguinte:
- Adicione uma nova restrição com a ação necessária.
- Exclua a restrição mais antiga que não tem a ação.
Isso evita um problema de Long-running Alter Constraint Operation
. Depois de criar
a nova chave externa com a ação ON DELETE CASCADE
, o efeito líquido de ambas
as restrições é DELETE CASCADE
. O descarte de uma restrição pode levar ao descarte
dos índices de chave externa de backup se eles não estiverem em uso em outras restrições
de chave estrangeira. Mais tarde, se o usuário adicionar a mesma restrição de chave externa com
ação, isso poderá exigir operações de longa duração, como preenchimento de índices,
validação de restrições de índice exclusivas e validação de restrições de referência chave externa.
Qualquer uma das alterações de esquema anteriores pode falhar se o índice referenciado não puder ser
criado devido a uma violação de restrição UNIQUE
.
É possível consultar INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.SPANNER_STATE
para
verificar o estado da criação de chave externa.
Validação de restrição
O Spanner valida as restrições de chave externa quando uma transação é confirmada ou quando os efeitos das gravações são visíveis para operações subsequentes na transação.
Um valor inserido nas colunas de referência é comparado com os valores da
tabela referenciada e das colunas referenciadas. As linhas com valores de referência NULL
não são verificadas, o que significa que elas podem ser adicionadas à tabela de referência.
O Spanner valida todas as restrições de referência de chave externa aplicável ao tentar atualizar dados usando instruções DML ou uma API. Todas as alterações pendentes serão revertidas se alguma restrição for inválida.
A validação ocorre imediatamente após cada instrução DML. Por exemplo, é necessário inserir a linha referenciada antes de inserir as linhas de referência. Ao usar uma API de mutação, as mutações são armazenadas em buffer até que a transação seja confirmada. A validação de chave estrangeira é adiada até a confirmação da transação. Nesse caso, é permitido inserir primeiro as linhas de referência.
Cada transação é avaliada quanto a modificações que afetam as restrições de chave estrangeira. Essas avaliações podem exigir outras solicitações ao servidor. Os índices de backup também exigem mais tempo de processamento para avaliar as modificações de transação e manter os índices. O armazenamento extra também é necessário para cada índice.
Índices de backup
As chaves estrangeiras não usam índices criados pelo usuário. Elas criam seus próprios índices de backup.
O Spanner pode criar até dois índices de backup secundários para cada chave externa: um para as colunas de referência e um segundo para as colunas referenciadas. No entanto, uma chave externa geralmente faz referência às chaves primárias da tabela referenciada. Portanto, o segundo índice na tabela referenciada normalmente não é necessário.
O índice de aplicação da tabela referenciada é um índice UNIQUE NULL_FILTERED
.
A criação da chave externa falhará se algum dado violar a restrição de exclusividade
do índice. O índice de backup da tabela de referência é NULL_FILTERED
.
Se duas ou mais chaves estrangeiras exigirem o mesmo índice de aplicação, o Spanner vai criar um único índice para todas elas. Os índices de backup são descartados quando as chaves estrangeiras que os utilizam são descartadas. Os usuários não podem alterar ou descartar os índices de backup.
O Spanner usa o esquema de informações de cada banco de dados para armazenar
metadados sobre índices de suporte. As linhas em INFORMATION_SCHEMA.INDEXES
que têm um valor SPANNER_IS_MANAGED
de true
descrevem índices de suporte.
Fora das consultas SQL que invocam diretamente o esquema de informações, o console do Google Cloud não mostra nenhuma informação sobre os índices de suporte de um banco de dados.
Ação de exclusão em cascata de longa duração
Quando você exclui uma linha de uma tabela referenciada, o Spanner precisa excluir todas as linhas nas tabelas de referência que se referem à linha excluída. Isso pode levar a um efeito cascata, em que uma única operação de exclusão resulta em milhares de outras operações de exclusão. Adicionar uma restrição de chave externa com ação de exclusão em cascata a uma tabela ou criar uma tabela com restrições de chave estrangeira com ação de exclusão em cascata pode retardar as operações de exclusão.
O limite de mutação foi excedido para a exclusão em cascata de chave externa
A exclusão de um grande número de registros usando uma exclusão em cascata chave externa pode afetar o desempenho. Isso acontece porque cada registro excluído invoca a exclusão de todos os registros relacionados a ele. Se você precisar excluir um grande número de registros usando uma exclusão em cascata de chave externa, exclua explicitamente as linhas das tabelas filhas antes de excluir a linha das tabelas mãe. Isso impede que a transação falhe devido ao limite de mutação.
Comparação de chaves estrangeiras e intercalação de tabelas
A intercalação de tabelas do Spanner é uma boa opção para muitas relações pai-filho em que a chave primária da tabela filha inclui as colunas de chave primária da tabela mãe. A colocalização das linhas filhas com as linhas mãe pode melhorar significativamente o desempenho.
As chaves externas são uma solução pai-filho mais geral e abordam casos de uso adicionais. Elas não estão limitadas a colunas de chave primária, e as tabelas podem ter várias relações de chave externa, como pai em alguns relacionamentos e filho em outros. No entanto, uma relação de chave estrangeira não sugere a colocalização das tabelas na camada de armazenamento.
Considere um exemplo que usa uma tabela Orders
definida da seguinte maneira:
Figura 3. Diagrama do nosso esquema de banco de dados com chaves estrangeiras
O design na Figura 3 tem algumas limitações. Por exemplo, cada pedido pode conter apenas um item.
Imagine que seus clientes querem pedir mais de um produto por
pedido. Você pode melhorar seu design introduzindo uma tabela OrderItems
que
contenha uma entrada para cada produto que o cliente pediu. Você pode apresentar
outra chave externa para representar essa nova relação de um para muitos entre
Orders
e OrderItems
. No entanto, você também sabe que muitas vezes é necessário executar consultas entre os pedidos e os respectivos itens de pedido. Como a colocalização desses dados melhora a performance, é recomendável criar a relação pai-filha usando o recurso de intercalação de tabelas do Spanner.
Veja como definir a tabela OrderItems
, intercalada com Orders
.
GoogleSQL
CREATE TABLE OrderItems (
OrderID INT64 NOT NULL,
ProductID INT64 NOT NULL,
Quantity INT64 NOT NULL,
FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
) PRIMARY KEY (OrderID, ProductID),
INTERLEAVE IN PARENT Orders ON DELETE CASCADE;
PostgreSQL
CREATE TABLE OrderItems (
OrderID BIGINT NOT NULL,
ProductID BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
FOREIGN KEY (ProductID) REFERENCES Products (ProductID),
PRIMARY KEY (OrderID, ProductID)
) INTERLEAVE IN PARENT Orders ON DELETE CASCADE;
A Figura 4 é uma representação visual do esquema de banco de dados atualizado como resultado
da introdução dessa nova tabela, OrderItems
, intercalada com Orders
. Aqui você
também pode ver a relação de um para muitos entre essas duas tabelas.
Figura 4 Adição de uma tabela intercalada "OrderItems"
Nessa configuração, é possível ter várias entradas OrderItems
em cada pedido,
e as entradas OrderItems
de cada pedido são intercaladas e, portanto,
colocalizadas com os pedidos. Intercalar fisicamente Orders
e OrderItems
dessa maneira pode melhorar o desempenho, mesclando previamente as tabelas e permitindo que você acesse linhas relacionadas, ao mesmo tempo que minimiza os acessos ao disco. Por exemplo,
o Spanner pode realizar mesclagens por chave primária localmente, minimizando o acesso
ao disco e o tráfego de rede.
Se o número de mutações em uma transação exceder 80.000, a transação vai falhar. Essas exclusões em cascata grandes funcionam bem para tabelas com uma relação "entrelaçada na mãe", mas não para tabelas com uma relação de chave externa. Se você tiver uma relação chave externa e precisar excluir um grande número de linhas, exclua primeiro as linhas das tabelas filhas.
Se você tiver uma tabela de usuário com uma relação de chave externa para outra tabela e a exclusão de uma linha da tabela referenciada acionar a exclusão de milhões de linhas, projete seu esquema com uma ação de exclusão em cascata com "intercalada na mãe".
Tabela de comparação
A tabela a seguir mostra um resumo comparando as chaves estrangeiras e a intercalação de tabelas. Use essas informações para decidir o que é melhor para seu projeto.
Tipo de relação pai-filho | Intercalação de tabelas | Chaves estrangeiras |
---|---|---|
Pode usar chaves primárias | Sim | Sim |
Pode usar colunas não primárias | Não | Sim |
Número de pais compatíveis | 0 .. 1 | 0 .. N |
Armazena dados pai e filho juntos | Sim | Não |
Compatível com exclusão em cascata | Sim | Sim |
Modo de correspondência nulo | Aprovado se todos os valores de referênciaforem iguais aos valores referenciados. Valores nulos não são diferentes de valores nulos. Os valores nulos são diferentes dos valores não nulos. |
Aprovado se algum valor de referência for nulo. Aprovado se todos os valores de referência não forem nulos e a tabela referenciada tiver uma linha com valores iguais aos valores de referência. Ocorre uma falha se nenhuma linha correspondente for encontrada. |
Tempo de aplicação | Por operação ao usar a API de mutação. Por instrução ao usar DML. |
Por transação ao usar a API de mutação. Por instrução ao usar DML. |
Pode ser removido | Não. Não é possível remover a intercalação de tabelas após a criação, a menos que você exclua a tabela filha inteira. | Sim |
A seguir
Saiba mais sobre Como criar e gerenciar relacionamentos de chave estrangeira.
Saiba mais sobre o esquema de informações.