Chaves estrangeiras

Neste tópico, descrevemos as chaves externas no Spanner e como elas podem ser usadas para aplicar a integridade referencial na solução de banco de dados.

Visão geral

As chaves externas permitem definir relações entre tabelas. Spanner. garante que a integridade dos dados dessas relações seja mantida.

Imagine que você seja o desenvolvedor líder de um negócio de comércio eletrônico. 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 feitos e uma tabela Products para armazenar informações sobre cada produto que os clientes possam pedir.

A figura 1 também mostra links entre as tabelas que mapeiam aos seguintes relacionamentos reais:

• Um cliente fez um pedido

• Um produto foi solicitado

Você decide que seu banco de dados precisa aplicar as regras a seguir para garantir que os pedidos em nosso 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 aplicamos essas regras, ou restrições, dizemos que estamos mantendo a integridade referencial dos nossos 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álidas entre os dados, falharão. A integridade referencial evita erros do usuário. O Spanner aplica a integridade referencial usando chaves externas.

Aplicar integridade referencial com chaves estrangeiras

Vejamos nosso exemplo de processamento de pedidos novamente, com mais detalhes adicionados ao projeto, conforme mostrado na Figura 2.

Figura 2. Diagrama do nosso 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 queremos 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 fez 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 todas as transações que tentam inserir ou atualizar uma linha da tabela Orders que tem um CustomerID ou ProductID não encontrado no 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 Restrição de transação validação abaixo.

Definir chaves estrangeiras

As chaves externas são criadas e removidas do banco de dados do Spanner usando DDL. As chaves estrangeiras são adicionadas a uma nova tabela com a instrução CREATE TABLE. Da mesma forma, é possível adicionar ou remover uma chave externa 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 relações de chaves estrangeiras.

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

Veja a seguir uma lista de características das chaves externas no Spanner.

• A tabela que define a chave estrangeira é a tabela de referência, e as colunas de chave estrangeira são as colunas de referência.

• A chave estrangeira faz referência às colunas referenciadas da tabela referenciada.

• Como no exemplo acima, você pode nomear cada restrição de chave estrangeira. Se você não especificarem um nome, o Spanner gera um nome automaticamente. O o nome gerado pode ser consultado no INFORMATION_SCHEMA do Spanner. Os nomes das restrições têm como escopo o esquema, além dos nomes das tabelas e índices, por exemplo. Eles precisam ser exclusivos dentro do esquema.

• O número de colunas de referência e referenciadas precisa ser o mesmo. A ordem é significativa. 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. As colunas também precisam ser indexáveis.

• 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 coluna "ManagerId" que faz referência à coluna "EmployeeId" 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 estrangeira. Portanto, pelo menos uma dessas chaves precisa ser adicionada usando a instrução ALTER TABLE.

• As chaves referenciadas precisam ser exclusivas. O Spanner usa o PRIMARY KEY do tabela referenciada se as colunas referenciadas da chave externa corresponderem ao colunas de chave primária da tabela referenciadas. Se o Spanner não puder usar o a chave primária da tabela referenciada, ele cria um UNIQUE NULL_FILTERED INDEX colunas referenciadas.

• O Spanner também pode usar a chave primária do modelo da tabela, embora isso seja menos comum. Caso contrário, o Spanner cria uma NULL_FILTERED INDEX sobre as colunas de referência.

• Chaves estrangeiras não usam índices secundários criados. Criam seus próprios índices de backup. Esses índices de backup podem ser usados em avaliações de consulta, incluindo em diretivas force_index explícitas. O nome dos índices de apoio podem ser consultados no INFORMATION_SCHEMA do Spanner. Para mais informações, consulte Índices de backup.

Ações de chave estrangeira

As ações de chave externa permitem controlar o que acontece com a coluna restrita quando a coluna referenciada for excluída ou atualizada. Spanner. suporta o uso da ação ON DELETE CASCADE. Com a chave externa ATIVADA DELETE CASCADE, quando você exclui uma linha que contém um idioma estrangeiro chave, 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 uma ação ao criar seu banco de dados usando DDL. Usar 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 de chave externa a um tabela ou para remover uma ação de chave externa. Confira a seguir um exemplo de como crie uma nova tabela com uma ação de 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
);

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

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

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

• Não é possível adicionar uma ação de chave externa a uma restrição atual suporte. Você precisa adicionar uma nova restrição de 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.

O Spanner precisa fazer o preenchimento de uma nova tabela com uma chave externa. os índices referenciados conforme necessário para cada chave externa.

Para uma tabela atual com uma chave externa, o Spanner precisa preencha as referências e os índices referenciados conforme necessário. Além disso, O Spanner valida os dados na tabela para garantir que eles estejam em conformidade com as a restrição de integridade referencial da chave externa. A mudança do esquema falha se algum dado for inválido.

Não é possível adicionar uma ação de chave externa a uma restrição atual. Qa sugerimos que você faça o seguinte:

1. Adicione uma nova restrição com ação.
2. Remover a restrição mais antiga sem 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 do é DELETE CASCADE. O descarte de uma restrição pode levar ao índices de suporte de chave estrangeira se os índices não estiverem em uso em outra chave externa restrições. Depois, se o usuário adicionar a mesma restrição de chave externa com isso pode exigir operações de longa duração, incluindo o preenchimento de índices, validação de restrições de índice único e validação de referencial de chave externa restrições.

Observação: se você não descartar a restrição de chave externa mais antiga, haverá sobrecarga devido para validar as restrições extras de chave externa.

Qualquer uma das alterações de esquema acima pode falhar se o índice referenciado não puder ser criado devido a uma violação de restrição UNIQUE.

Consulte INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.SPANNER_STATE para verificar o estado da criação de chave estrangeira.

Validação de restrição para uma transação

O Spanner valida restrições de chave externa quando uma transação está sendo comprometido ou quando os efeitos das gravações estão se tornando visíveis para operações na transação.

Um valor inserido nas colunas de referência é comparado com os valores da tabela referenciada e das colunas referenciadas. Linhas com NULL que fazem referência a valores não estão marcadas, ou seja, é 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 por meio de instruções DML ou de 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 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 apoio secundários para cada uma para as colunas de referência e uma segunda para as colunas referenciadas. No entanto, uma chave estrangeira 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 backup da tabela referenciada é um índice UNIQUE NULL_FILTERED. A criação da chave estrangeira 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 índice para todos eles. 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 apoio. Linhas em INFORMATION_SCHEMA.INDEXES com um valor SPANNER_IS_MANAGED de true descrevem os índices de apoio.

Fora das consultas SQL que invocam diretamente o esquema de informações, a classe O console do Google Cloud não exibe nenhuma informação sobre os índices de apoio de um banco de dados.

Ação em cascata de exclusão 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 fazem referência à linha excluída. Isso pode levar com efeito em cascata, em que uma única operação de exclusão pode resultar de outras operações de exclusão. Como adicionar uma restrição de chave externa com cascata de exclusão em uma tabela ou criando uma tabela com restrições chave externa com delete a ação em cascata pode retardar as operações de exclusão.

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

Excluir um grande número de registros usando uma cascata de exclusão de chave externa pode impactar o desempenho. Isso ocorre porque cada registro excluído aciona o exclusão de todos os registros relacionados a ele que estejam usando chaves. Se o número de mutações em uma transação exceder 80.000, a transação vai falhar.

Se você precisar excluir um grande número de registros usando uma chave externa, exclua cascata, você deve excluir explicitamente as linhas das tabelas-filho antes excluindo a linha das tabelas pai. Isso evita que a transação devido ao limite de mutações.

Comparação de chaves estrangeiras e intercalação de tabelas

A intercalação de tabelas do Spanner é uma boa opção para vários modelos relações em que a chave primária da tabela filha inclui o valor colunas de chave primária. A localização mútua das linhas filho com as linhas pai pode melhorar significativamente o desempenho.

As chaves estrangeiras são uma solução pai-filho mais geral e abordam outros casos de uso. Elas não estão limitadas a colunas de chave primária, e as tabelas podem ter várias relações de chave estrangeira, 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.

Vejamos um exemplo, usando o esquema de processamento de pedidos discutido anteriormente neste tópico. Lembre-se de que nossa tabela Orders foi 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 nossos clientes nos diga que querem solicitar mais de um produto por pedido. Podemos melhorar nosso design introduzindo uma tabela OrderItems que contenha uma entrada para cada produto solicitado pelo cliente. Podemos apresentar outra chave estrangeira para representar essa nova relação de um para muitos entre Orders e OrderItems. No entanto, também sabemos que boa parte do tempo é necessário executar consultas entre os pedidos e os respectivos itens de pedido. A colocation desses dados melhoraria o desempenho, então criaremos o relacionamento pai-filho usando o recurso de intercalação de tabelas do Spanner.

Veja como definimos 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 haver 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. Para por exemplo, o Spanner pode realizar junções por chave primária localmente, minimizando acesso ao disco e 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 "intercalado na mãe" relação, mas não para tabelas com uma chave externa relação. Se você tem um relacionamento de chave externa e precisa excluir um um grande número de linhas, é preciso excluir explicitamente as linhas do tabelas.

Se você tem uma tabela de usuário com uma relação chave externa com outra tabela e excluir uma linha da tabela referenciada aciona a exclusão de milhões de linhas, você deve projetar seu esquema com uma ação em cascata de exclusão com "intercalado na mãe".

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 ambos é redundante e pode consumir mais armazenamento e recursos de computação para verificar restrições e manter índices de backup.

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 facilmente	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.