Chaves externas

Neste documento, descrevemos as chaves externas no Spanner e como usá-las para aplicar a integridade referencial no banco de dados. Os tópicos a seguir ajudam você a aprender mais sobre chaves externas e como usá-las:

• Visão geral das chaves externas no Spanner
• Tipos de chaves externas
• Comparação de tipos de chave externa
• Escolher o tipo de chave externa a ser usado
• Usar chaves externas aplicadas
• Usar chaves externas informativas
• Índices de aplicação
• Mudanças de esquema de longa duração

Visão geral das chaves externas no Spanner

As chaves externas definem as relações entre as tabelas. É possível usar chaves externas para garantir que a integridade de dados desses relacionamentos no Spanner 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 de 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 rastrear 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 são mapeadas para as seguintes relações do mundo real:

• Um cliente faz um pedido.

• Um pedido é feito para um produto.

Você decide que seu banco de dados aplica as seguintes regras para garantir que os pedidos no 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.

Ao aplicar essas regras, ou restrições, você mantém a integridade referencial dos dados. Quando um banco de dados mantém a integridade referencial, todas as tentativas de adicionar dados inválidos, que resultariam em links ou referências inválidas entre dados, falham. A integridade referencial evita erros do usuário. Por padrão, o Spanner usa chaves externas para garantir a integridade referencial.

Definir a integridade referencial com chaves externas

A seguir, analisamos o exemplo de 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 externas

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 espera que todas as linhas em Orders tenham um CustomerId válido. A chave externa FK_ProductOrder espera que todos os valores ProductId na tabela Orders sejam válidos. A tabela a seguir mapeia essas restrições de volta para as regras do mundo real que você quer aplicar.

Nome da chave estrangeira	Restrição	Descrição real
FK_CustomerOrder	Espera que todas as linhas em Orders tenham um CustomerId válido.	Um cliente válido faz um pedido
FK_ProductOrder	Espera que todas as linhas em Orders tenham um ProductId válido.	Um produto válido foi solicitado

O Spanner aplica restrições especificadas usando chaves externas aplicadas. Isso significa que 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.

Ao contrário das chaves externas aplicadas, o Spanner não valida restrições em chaves externas informativas. Isso significa que, se você usar uma chave externa informativa nesse cenário, uma transação que tentar inserir ou atualizar uma linha na tabela Orders que tenha um CustomerId ou ProductId não encontrado nas tabelas Customers e Products não será validada e a transação não falhará. Além disso, ao contrário das chaves externas aplicadas, as chaves externas informativas são compatíveis apenas com o GoogleSQL, e não com o PostgreSQL.

Características da chave externa

Veja a seguir uma lista de características de chaves externas 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 externa faz referência às colunas referenciadas da tabela referenciada.

• Como no exemplo, é possível nomear cada restrição de chave externa. Se você não especificar um nome, o Spanner vai gerar um para você. É possível consultar o nome gerado na INFORMATION_SCHEMA do Spanner. Os nomes de restrição têm escopo definido para o esquema, junto com os nomes de tabelas e índices, e precisam ser exclusivos no esquema.

• O número de colunas de referência e referenciadas precisa ser o mesmo. A ordem é importante. Por exemplo, a primeira coluna de referência se refere à primeira coluna referenciada, e a segunda coluna de referência se refere à segunda coluna referenciada.

• As colunas de referência e a contraparte referenciada precisam ser do mesmo tipo. É preciso ser capaz de indexar as colunas.

• Não é possível criar chaves externas 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 autorreferenciada). Um exemplo é uma tabela Employee com uma coluna ManagerId que faz referência à coluna EmployeeId da tabela.

• As chaves externas também podem formar relações circulares entre tabelas em que duas tabelas se referenciam, direta ou indiretamente. A tabela referenciada precisa existir antes de criar 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 usa o PRIMARY KEY da tabela referenciada se as colunas referenciadas de uma chave externa corresponderem às colunas de chave primária da tabela referenciada. Se o Spanner não puder usar a chave primária da tabela referenciada, ele vai criar um UNIQUE NULL_FILTERED INDEX nas colunas referenciadas.

• As chaves externas não usam índices secundários que você criou. Em vez disso, eles criam os próprios índices de aplicação. Os índices de aplicação podem ser usados em avaliações de consulta, incluindo em diretivas force_index explícitas. É possível consultar os nomes dos índices de aplicação no INFORMATION_SCHEMA do Spanner. Para mais informações, consulte Índices de aplicação.

Tipos de chaves externas

Há dois tipos de chaves externas: aplicadas e informativas. As chaves externas aplicadas são o padrão e aplicam a integridade referencial. As chaves externas informativas não impõem integridade referencial e são mais usadas para declarar o modelo de dados lógico pretendido para otimização de consultas. Para mais detalhes, consulte as seções de chaves externas aplicadas e informativas a seguir e a tabela de comparação de tipos de chave externa externas.

Chaves externas aplicadas

As chaves externas aplicadas, o tipo padrão no Spanner, aplicam a integridade referencial. Como as chaves externas aplicadas impõem a integridade referencial, elas causam falhas nas tentativas de:

• Não é possível adicionar uma linha a uma tabela de referência que tenha um valor de chave externa que não exista na tabela referenciada.

• A exclusão de uma linha de uma tabela referenciada por linhas na tabela de referência falha.

Todas as chaves externas do PostgreSQL são aplicadas. As chaves externas do GoogleSQL são aplicadas por padrão. Como as chaves externas são aplicadas por padrão, o uso da palavra-chave ENFORCED para especificar que uma chave externa do GoogleSQL é aplicada é opcional.

Chaves externas informativas

As chaves externas informativas são usadas para declarar o modelo de dados lógicos pretendido para otimização de consultas. Embora as chaves de tabela referenciadas precisem ser exclusivas para chaves externas informativas, a integridade referencial não é aplicada. Se você quiser validar seletivamente a integridade referencial ao usar chaves externas informativas, gerencie a lógica de validação no lado do cliente. Para mais informações, consulte Usar chaves externas informativas.

Use a palavra-chave NOT ENFORCED para especificar que uma chave externa do GoogleSQL é informativa. O PostgreSQL não é compatível com chaves externas informativas.

Comparação de tipos de chave externa

As restrições aplicadas e informativas têm benefícios. As seções a seguir comparam os dois tipos de chaves externas e incluem algumas práticas recomendadas.

Diferenças de alto nível entre chave externa

Em um nível geral, estas são algumas das diferenças entre as chaves externas aplicadas e informativas:

• Aplicação. As chaves externas aplicadas validam e garantem a integridade referencial nas gravações. As chaves externas informativas não validam nem garantem a integridade referencial.

• Storage. As chaves externas aplicadas podem exigir mais armazenamento para o índice de aplicação na tabela restrita.

• Capacidade de gravação. As chaves externas aplicadas podem gerar mais sobrecarga no caminho de gravação do que as chaves externas informativas.

• Otimização de consultas. Os dois tipos de chaves externas podem ser usados para otimização de consultas. Quando o otimizador tem permissão para usar chaves externas informativas, os resultados da consulta podem não refletir os dados reais se eles não corresponderem às relações de chave externa informativas (por exemplo, se algumas chaves restritas não tiverem chaves referenciadas correspondentes na tabela referenciada).

Tabela de diferenças de chave externa

A tabela a seguir lista as diferenças detalhadas entre chaves externas aplicadas e informativas:

	Chaves externas aplicadas	Chaves externas informativas
Palavras-chave	ENFORCED	NOT ENFORCED
Compatível com o GoogleSQL	Sim. As chaves externas no GoogleSQL são aplicadas por padrão.	Sim.
Compatível com PostgreSQL	Sim. As chaves externas no PostgreSQL só podem ser aplicadas.	Não.
Armazenamento	As chaves externas aplicadas exigem armazenamento para até dois índices de aplicação.	As chaves externas informativas exigem armazenamento para até um índice de aplicação.
Cria índices de aplicação em colunas de tabela referenciadas quando necessário	Sim.	Sim.
Cria índices de aplicação em colunas de tabela de referência quando necessário	Sim.	Não.
Suporte a ações de chave externa	Sim.	Não.
Valida e aplica a integridade referencial	Sim.	Não. Não ter validação melhora o desempenho de gravação, mas pode afetar os resultados da consulta quando chaves externas informativas são usadas para otimização de consultas. É possível usar a validação do lado do cliente ou uma chave externa aplicada para garantir a integridade referencial.

Escolher o tipo de chave externa a ser usado

Use as diretrizes a seguir para decidir qual tipo de chave externa usar:

Recomendamos que você comece com chaves externas aplicadas. As chaves externas aplicadas mantêm os dados e o modelo lógico consistentes o tempo todo. As chaves externas aplicadas são a opção recomendada, a menos que não funcionem para seu caso de uso.

Recomendamos que você considere as chaves externas informativas se cada uma das seguintes condições for verdadeira:

• Você quer usar o modelo de dados lógico descrito pela chave externa informativa na otimização de consultas.

• Manter uma integridade referencial estrita é impraticável ou afeta significativamente o desempenho. Veja a seguir exemplos de quando você pode considerar o uso de uma chave externa informativa:

  • Sua fonte de dados upstream segue um modelo de consistência posterior. Nesse caso, as atualizações feitas no sistema de origem podem não ser refletidas imediatamente no Spanner. Como as atualizações podem não ser imediatas, podem ocorrer breves inconsistências nas relações chave externa.

  • Seus dados contêm linhas referenciadas que têm um grande número de relacionamentos de referência. As atualizações dessas linhas podem usar muitos recursos porque o Spanner precisa validar ou, em alguns casos, excluir todas as linhas relacionadas à manutenção da integridade referencial. Nesse cenário, as atualizações podem afetar o desempenho do Spanner e desacelerar as transações simultâneas.

• Seu aplicativo pode lidar com possíveis inconsistências de dados e o impacto delas nos resultados da consulta.

Usar chaves externas informativas

Os tópicos a seguir são apenas para chaves externas informativas. Para tópicos que se aplicam a chaves externas informativas e aplicadas, consulte o seguinte:

• Mudanças de esquema de longa duração
• Índices de aplicação

Criar uma tabela com uma chave externa informativa

Você cria e remove chaves externas informativas do seu banco de dados do Spanner usando instruções DDL. Adicione chaves externas a uma nova tabela com a instrução CREATE TABLE. Da mesma forma, é possível adicionar ou remover chaves externas de uma tabela com a instrução ALTER TABLE.

O exemplo a seguir cria uma nova tabela com uma chave externa informativa usando o GoogleSQL. As chaves externas informativas não são compatíveis com o PostgreSQL.

GoogleSQL

CREATE TABLE Customers (
  CustomerId INT64 NOT NULL,
  CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

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) NOT ENFORCED
 ) PRIMARY KEY (OrderId);

PostgreSQL

Not Supported

Para mais exemplos de como criar e gerenciar chaves externas, consulte Criar e gerenciar relações de chave externa. Para mais informações sobre instruções DDL, consulte a referência de DDL.

Observação: também é possível adicionar e remover chaves externas usando o console do Google Cloud . Selecione Editar como texto na interface de criação de tabelas e insira a DDL para gerenciar chaves externas na tabela.

Usar chaves externas informativas para otimização de consultas

As chaves externas aplicadas e as chaves externas informativas podem ser usadas pelo otimizador de consultas para melhorar o desempenho da consulta. O uso de chaves externas informativas permite que você aproveite os planos de consulta otimizados sem a sobrecarga da aplicação estrita da integridade referencial.

Se você ativar o otimizador de consultas para usar as informações de chaves externas informativas, é importante entender que a correção da otimização depende de dados consistentes com o modelo lógico descrito pelas chaves externas informativas. Se houver inconsistências, os resultados da consulta poderão não refletir os dados reais. Um exemplo de inconsistência é quando um valor em uma coluna restrita não tem um valor correspondente em uma coluna referenciada.

Por padrão, o otimizador de consultas usa chaves externas NOT ENFORCED. Para mudar isso, defina a opção de banco de dados use_unenforced_foreign_key_for_query_optimization como falsa. Confira a seguir um exemplo do GoogleSQL que demonstra isso (as chaves externas informativas não estão disponíveis no PostgreSQL):

SET DATABASE OPTIONS (
    use_unenforced_foreign_key_for_query_optimization = false
);

A dica de instrução de consulta booleana @{use_unenforced_foreign_key} substitui a opção de banco de dados por consulta que controla se o otimizador usa chaves externas NOT ENFORCED. Desativar essa dica ou a opção de banco de dados pode ser útil ao solucionar problemas de resultados de consulta inesperados. Veja como usar @{use_unenforced_foreign_key}:

@{use_unenforced_foreign_key=false} SELECT Orders.CustomerId
    FROM Orders
    INNER JOIN Customers ON Customers.CustomerId = Orders.CustomerId;

Usar chaves externas aplicadas

Os tópicos a seguir são apenas para chaves externas aplicadas. Para tópicos que se aplicam a chaves externas informativas e aplicadas, consulte o seguinte:

• Mudanças de esquema de longa duração
• Índices de aplicação

Criar uma tabela com uma chave externa aplicada

Você cria e remove chaves externas aplicadas do banco de dados do Spanner usando DDL. Adicione chaves externas a uma nova tabela com a instrução CREATE TABLE. Da mesma forma, é possível adicionar ou remover uma chave externa de uma tabela com a instrução ALTER TABLE.

Você cria e remove chaves externas do banco de dados do Spanner usando DDL. Adicione chaves externas a uma nova tabela com a instrução CREATE TABLE. Da mesma forma, é possível adicionar ou remover uma chave externa de uma tabela com a instrução ALTER TABLE.

Veja a seguir um exemplo de como criar uma tabela com uma chave externa aplicada.

GoogleSQL

CREATE TABLE Customers (
CustomerId INT64 NOT NULL,
CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

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) ENFORCED
) PRIMARY KEY (OrderId);

PostgreSQL

CREATE TABLE Customers (
CustomerId bigint NOT NULL,
CustomerName character varying(1024) NOT NULL,
PRIMARY KEY(CustomerId)
);

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 externas, consulte Criar e gerenciar relações de chave externa.

Observação: também é possível adicionar e remover chaves externas usando o console do Google Cloud . Selecione editar como texto na interface de criação de tabelas e insira a DDL para gerenciar chaves externas da tabela.

Ações de chave externa

As ações de chave externa só podem ser definidas em chaves externas aplicadas.

As ações de chave externa controlam o que acontece com a coluna restrita quando a coluna que ela referencia é excluída ou atualizada. O Spanner é compatível com o uso das ações ON DELETE CASCADE e ON DELETE SET NULL.

Com a ação ON DELETE CASCADE, quando você exclui uma linha referenciada por uma chave externa, todas as linhas que fazem referência a essa chave também são excluídas na mesma transação.

Com a ação ON DELETE SET NULL, quando você exclui uma linha referenciada por uma chave externa, as colunas de referência em todas as linhas que fazem referência a essa chave são definidas como NULL 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 externas com uma ação a uma nova tabela. Da mesma forma, é possível usar a instrução ALTER TABLE para adicionar uma ação de chave externa a uma tabela atual ou para remover uma ação de chave externa. Veja a seguir um exemplo de como criar uma nova tabela com uma ação de chave externa.

GoogleSQL

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 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
);

Veja a seguir uma lista de características das ações de chave externa no Spanner.

• As ações de chave externa são ON DELETE CASCADE, ON DELETE SET NULL ou ON DELETE NO ACTION.

• Não é possível usar ON DELETE SET NULL em colunas de referência que fazem parte da chave primária da tabela de referência, marcadas como NOT NULL ou colunas geradas.

• É possível consultar INFORMATION_SCHEMA para encontrar restrições de chave externa que têm uma ação.

• Não é possível adicionar uma ação de chave externa em uma restrição de chave externa existente. É necessário adicionar uma nova restrição de chave externa com uma ação.

Validação de restrição

A validação de restrição se aplica apenas a chaves externas aplicadas.

O Spanner valida as restrições de chave externa aplicadas quando uma transação é confirmada ou quando os efeitos das gravações são visíveis para as operações subsequentes na transação.

Um valor inserido na coluna de referência é comparado com os valores da tabela e das colunas referenciadas. As linhas com valores de referência NULL não são verificadas, o que significa que é possível adicioná-las à tabela de referência.

O Spanner valida todas as restrições referenciais de chave externa aplicáveis 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, é preciso 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 externa aplicada é adiada até que a transação seja confirmada. Nesse caso, é permitido inserir as linhas de referência primeiro.

Cada transação é avaliada para modificações que afetam as restrições de chave externa aplicadas. Essas avaliações podem exigir outras solicitações ao servidor. Os índices de aplicação também exigem mais tempo de processamento para avaliar as modificações de transação e manter os índices. Também é necessário armazenamento adicional para cada índice.

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 em 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 externa com ação de exclusão em cascata pode deixar as operações de exclusão mais lentas.

Ação de exclusão de longa duração para definir como nulo

Quando você exclui uma linha de uma tabela referenciada, o Spanner atualiza todas as linhas nas tabelas de referência que têm uma ação ON DELETE SET NULL definindo as colunas de referência como NULL. Isso pode levar a um efeito em cascata, em que uma única operação de exclusão resulta em milhares de operações de atualização. Adicionar uma restrição de chave externa ON DELETE SET NULL a uma tabela ou criar uma tabela com uma pode deixar as operações de exclusão mais lentas.

Limite de mutação excedido para exclusão em cascata de chave externa

Excluir um grande número de registros usando uma exclusão em cascata de chave externa pode afetar o desempenho. Isso ocorre 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 pai. Isso evita que a transação falhe devido ao limite de mutação.

O limite de mutação foi excedido para a exclusão de chave externa definida como nula

Excluir linhas que acionam um grande número de atualizações usando uma ação ON DELETE SET NULL de chave externa pode afetar o desempenho. Isso ocorre porque cada linha excluída na tabela referenciada aciona a atualização de todas as linhas que a referenciam na tabela de referência. Se você precisar excluir uma linha que aciona muitas atualizações usando ON DELETE SET NULL, atualize explicitamente as linhas de referência para NULL ou exclua-as antes de excluir a linha referenciada. Essa prática evita que a transação falhe devido ao limite de mutação.

Uso de recursos para ações de exclusão de chave externa

ON DELETE CASCADE e ON DELETE SET NULL têm custos de recursos e mutação diferentes, dependendo do esquema:

• Mutações da tabela base: ON DELETE SET NULL atualiza colunas individuais, portanto, cada linha de referência atualizada conta como várias mutações (para a chave de linha e cada coluna definida como NULL). ON DELETE CASCADE conta como uma mutação por linha excluída ou uma mutação por intervalo de chaves excluído quando as colunas de referência são um prefixo da chave primária da tabela de referência.
• Mutações de índice: as duas ações usam uma mutação de exclusão por linha no índice de aplicação da chave externa. Para outros índices secundários na tabela de referência, ON DELETE SET NULL modifica apenas os índices que incluem as colunas de referência, enquanto ON DELETE CASCADE exclui entradas de todos os índices secundários na tabela.
• Leituras: o ON DELETE SET NULL sempre lê o índice de aplicação da chave externa para encontrar linhas correspondentes. O ON DELETE CASCADE pode pular essa leitura e excluir um intervalo de chaves diretamente quando as colunas de referência são um prefixo da chave primária da tabela de referência.
• Em cascata: ON DELETE SET NULL nunca é aplicado em cascata a tabelas filhas intercaladas porque as colunas de chave primária não podem ser definidas como NULL. Ele só aciona outras ações se outra chave externa fizer referência à coluna atualizada. ON DELETE CASCADE exclui toda a linha de referência, então também é aplicada em cascata a todas as tabelas filhas intercaladas com ON DELETE CASCADE e às chaves externas downstream.

Comparação entre chaves externas aplicadas e intercalação de tabelas

A intercalação de tabelas do Spanner é uma boa opção para muitos relacionamentos em que a chave primária da filha inclui colunas da chave primária da mãe. A colocalização das linhas filhas com as linhas mãe pode melhorar bastante o desempenho.

As chaves externas são uma solução pai-filho mais geral e abordam casos de uso adicionais. Elas não se limitam a colunas chave primária, e as tabelas podem ter várias relações de chave externa, tanto como pai em algumas relações quanto como filho em outras. 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 esquema do banco de dados com chaves externas aplicadas

O design na Figura 3 tem algumas limitações. Por exemplo, cada pedido pode conter apenas um item.

Imagine que seus clientes queiram pedir mais de um produto por pedido. É possível melhorar o design introduzindo uma tabela OrderItems que contém uma entrada para cada produto que o cliente pediu. É possível introduzir outra chave externa aplicada para representar essa nova relação de um para muitos entre Orders e OrderItems. No entanto, você também sabe que, muitas vezes, quer executar consultas em pedidos e nos respectivos itens. Como a colocalização desses dados aumenta o desempenho, é recomendável criar a relação pai-filho usando o recurso de intercalação de tabelas do Spanner.

Veja como definir a tabela OrderItems, intercalada com Orders.

GoogleSQL

CREATE TABLE Products (
ProductId INT64 NOT NULL,
Name STRING(256) NOT NULL,
Price FLOAT64
) PRIMARY KEY(ProductId);

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 Products (
ProductId BIGINT NOT NULL,
Name varchar(256) NOT NULL,
Price float8,
PRIMARY KEY(ProductId)
);

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, colocadas no mesmo local que os pedidos. A intercalação física de Orders e OrderItems dessa maneira pode melhorar o desempenho, pré-unindo as tabelas e permitindo que você acesse linhas relacionadas juntas, minimizando 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 funcionam bem para tabelas com uma relação "intercalada no pai", mas não para tabelas com uma relação de chave externa. Se você tiver uma relação de chave externa e precisar excluir um grande número de linhas, exclua explicitamente as linhas das tabelas filhas primeiro.

Se você tiver uma tabela de usuários com uma relação de chave externa com 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 "intercalado no pai".

Observação: o Google recomenda que você escolha representar relações pai-filho como tabelas intercaladas ou como chaves externas, mas não ambas. Usar os dois é redundante e pode consumir mais recursos de armazenamento e computação para verificar restrições e manter índices de aplicação.

Tabela de comparação

A tabela a seguir resume como as chaves externas aplicadas e a intercalação de tabelas se comparam. Use essas informações para decidir o que é melhor para seu projeto.

Tipo de relação pai-filho	Intercalação de tabelas	Chaves externas aplicadas
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. A intercalação de tabelas não pode ser removida depois de criada, a menos que você exclua toda a tabela filha.	Sim

Índices de aplicação

As chaves externas não usam índices criados pelo usuário. Em vez disso, elas criam os próprios índices de aplicação. As chaves externas aplicadas e informativas criam índices de aplicação de maneira diferente no Spanner:

• Para chaves externas aplicadas, o Spanner pode criar até dois índices de aplicação secundários para cada chave externa, um para as colunas de referência e outro para as colunas referenciadas.

• Para chaves externas informativas, o Spanner pode criar até um índice de aplicação quando necessário para as colunas referenciadas. As chaves externas informativas não criam um índice de aplicação para as colunas de referência.

Para chaves externas aplicadas e informativas, uma chave externa geralmente faz referência às chaves primárias da tabela referenciada. Portanto, um índice para a tabela referenciada normalmente não é necessário. Por isso, as chaves externas informativas normalmente têm zero índices de aplicação. Quando necessário, o índice de aplicação criado para a 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.

As chaves externas informativas não têm um índice de aplicação para a tabela de referência. Para chaves externas aplicadas, o índice de aplicação da tabela de referência é NULL_FILTERED.

Se duas ou mais chaves externas exigirem o mesmo índice de aplicação, o Spanner vai criar um único índice para cada uma delas. Os índices de aplicação são descartados quando as chaves externas que os usam são descartadas. Não é possível alterar ou descartar os índices de aplicação.

O Spanner usa o esquema de informações de cada banco de dados para armazenar metadados sobre índices de aplicação. As linhas em INFORMATION_SCHEMA.INDEXES que têm um valor SPANNER_IS_MANAGED de true descrevem índices de aplicação.

Fora das consultas SQL que invocam diretamente o esquema de informações, o console doGoogle Cloud não exibe nenhuma informação sobre os índices de aplicação de um banco de dados.

Alterações de esquema de longa duração

Adicionar uma chave externa aplicada a uma tabela ou criar uma nova tabela com uma chave externa pode levar a operações de longa duração. No caso de uma nova tabela, ela não será gravável até que a operação de longa duração seja concluída.

A tabela a seguir mostra o que acontece no Spanner quando uma chave externa aplicada e uma informativa estão em uma tabela nova ou atual:

Tipo de tabela	Chave externa aplicada	Chave externa informativa
Novo	O Spanner preenche os índices referenciados conforme necessário para cada chave externa.	O Spanner preenche os índices referenciados conforme necessário para cada chave externa.
Atual	O Spanner preenche os índices de referência e referenciados conforme necessário. O Spanner também valida os dados na tabela para garantir que eles estejam em conformidade com a restrição de integridade referencial da chave externa. A mudança de esquema vai falhar se houver dados inválidos.	O Spanner preenche o índice referenciado conforme necessário e não valida os dados atuais na tabela.

Não há compatibilidade com os seguintes recursos:

• Adicionar uma ação de chave externa a uma restrição de chave externa aplicada.
• Alterar a aplicação de uma chave externa.

Em ambos os casos, recomendamos que você faça o seguinte:

1. Adicione uma nova restrição com a ação ou aplicação necessária.
2. Remova a restrição antiga.

Adicionar uma nova restrição e descartar a antiga evita um problema de operação de alteração de restrição de longa duração. Por exemplo, suponha que você queira adicionar uma ação DELETE CASCADE em uma chave externa. Depois de criar a nova chave externa com a ação ON DELETE CASCADE, o efeito de ambas as restrições é uma ação DELETE CASCADE. Em seguida, você pode remover a restrição antiga com segurança.

A remoção de uma restrição pode levar à remoção dos índices de aplicação de chave externa se eles não forem usados por outras restrições de chave externa. Por isso, se você remover a restrição antiga primeiro, adicionar a mesma restrição de chave externa com uma ação mais tarde poderá levar a operações de longa duração, como preenchimento de índices, validação de restrições de índice exclusivas ou validação de restrições referenciais de chave externa.

Observação: se você não remover a restrição de chave externa mais antiga, as restrições de chave externa extras precisarão de validação, o que causa uma sobrecarga desnecessária. Além disso, se uma violação de restrição UNIQUE impedir a criação do índice referenciado, a adição da nova restrição poderá falhar.

É possível consultar INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.SPANNER_STATE para verificar o estado de criação da chave externa.

A seguir

• Saiba mais sobre Como criar e gerenciar relacionamentos de chave estrangeira.

• Saiba mais sobre o esquema de informações.