Como criar e gerenciar relações de chave estrangeira

Nesta página, descrevemos como gerenciar relações de chave externa no banco de dados.

As chaves externas permitem definir relações entre tabelas. O Cloud Spanner garante que a integridade referencial entre essas tabelas seja mantida. O diagrama a seguir ilustra um esquema de banco de dados simples que usaremos neste guia.

Figura 1. Diagrama de um esquema de banco de dados de processamento de pedidos

Há três tabelas no esquema mostrado na figura 1:

• A tabela Customers registra os nomes de cada cliente.
• As tabelas Orders registram todos os pedidos feitos.
• A tabela Products armazena as informações de cada produto.

Há duas relações de chave externa entre essas tabelas:

• Um relacionamento de chave externa é definido entre a tabela Orders e a tabela Customers para garantir que um pedido não seja criado a menos que haja um cliente correspondente.

• Uma relação de chave externa entre a tabela Orders e a tabela Products garante que não seja possível criar um pedido para um produto que não existe.

Com esse esquema como referência, vejamos as instruções da linguagem de definição de dados (DDL) que você pode usar para gerenciar essas restrições no banco de dados.

Como adicionar uma chave externa ao criar uma nova tabela

Vamos supor que já tenhamos criado a tabela Customers em nosso banco de dados simples de pedidos de produtos. Precisamos de uma tabela Orders para armazenar informações sobre os pedidos que os clientes fazem. Para garantir que todos os pedidos sejam válidos, não queremos permitir que o sistema insira linhas na tabela "Pedidos" que não tenham uma entrada correspondente na tabela Customers.

Veja a instrução DDL CREATE TABLE da tabela Orders que inclui a restrição de chave externa que faz referência à tabela Customers.

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

A instrução anterior contém uma cláusula CONSTRAINT que podemos descrever da seguinte maneira:

• O uso da sintaxe CONSTRAINT permite nomear uma restrição, facilitando a remoção usando o nome escolhido.

• A restrição tem o nome FK_CustomerOrder. Os nomes das restrições têm escopo no esquema e precisam ser exclusivos dentro do esquema.

• A tabela Orders, na qual definimos a restrição, é chamada de tabela de referência. A tabela Customers é a tabela referenciada.

• A coluna de referência na tabela de referência é CustomerID. Ela faz referência ao campo CustomerID na tabela Customers. Se alguém tentar inserir uma linha em Orders com um CustomerID que não existe em Customers, a inserção falhará.

Observação: geralmente, as colunas referenciadas da tabela referenciada correspondem às colunas de chave primária, mas isso não é obrigatório. Uma tabela de referência também pode fazer referência a colunas não principais de uma tabela referenciada.

O snippet a seguir mostra uma instrução de criação de tabela alternativa. Aqui, a restrição de chave externa é definida sem um nome. Quando você usa essa sintaxe, o Cloud Spanner gera um nome para você. Para descobrir os nomes de todas as chaves externas, consulte Exibir propriedades de uma relação de chave externa.

CREATE TABLE Orders (
  OrderID INT64 NOT NULL,
  CustomerID INT64 NOT NULL,
  ProductID INT64 NOT NULL,
  FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
) PRIMARY KEY (OrderID);

Como adicionar uma chave externa a uma tabela existente

Também queremos garantir que só possam ser criados pedidos de produtos existentes. Usaremos ALTER TABLE para adicionar outra restrição de chave externa à tabela de pedidos da seguinte maneira:

ALTER TABLE Orders
  ADD CONSTRAINT FK_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID);

A coluna de referência em Orders é ProductID e faz referência à coluna ProductID em "Produtos". Novamente, se você estiver bem com o Cloud Spanner nomeando essas restrições para você, use a seguinte sintaxe:

ALTER TABLE Orders
  ADD FOREIGN KEY (ProductID) REFERENCES Products (ProductID);

Como consultar dados em relações de chave estrangeira

SELECT * FROM Orders
  INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
  INNER JOIN Products ON Orders.ProductsID = Products.ProductID;

Exemplo de integridade referencial

O principal motivo para adicionar relações de chave externa é para que o Cloud Spanner possa manter a integridade referencial dos dados. Se você modificar os dados de uma maneira que interrompa uma restrição de chave externa, a atualização falhará com um erro.

Considere os dados na figura 2 abaixo. Alguns clientes solicitaram produtos, conforme mostrado na tabela de pedidos. Devido às nossas chaves externas em vigor, podemos garantir que os dados inseridos na tabela Orders tenham integridade referencial.

Figura 2. Dados de amostra do nosso banco de dados de pedidos.

Vejamos o que acontece quando tentamos modificar os dados de uma forma que viola a integridade referencial.

Como adicionar uma linha à tabela Orders com um valor CustomerID que não existe em Customers

O que acontece se tentarmos a seguinte modificação, considerando os dados de amostra do diagrama anterior?

INSERT INTO Orders (OrderID, ProductID, Quantity, CustomerID)
  VALUES (19, 337876, 4, 447);

Neste caso, estamos tentando inserir uma linha em Orders com um CustomerID (447) que não existe na tabela Customers. Se permitirmos isso, teremos um pedido inválido em nosso sistema. No entanto, a restrição de chave externa que definimos da tabela Orders para a tabela Customers nos protege, e o INSERT falha com mensagem a seguir, supondo que a restrição se chame FK_CustomerOrder.

Foreign key constraint `FK_CustomerOrder` is violated on table `Orders`. Cannot find referenced values in Customers(CustomerID).

Como tentar excluir uma linha da tabela Customers quando o cliente é referenciado em uma restrição de chave estrangeira.

Vamos imaginar uma situação em que um cliente cancela a inscrição da nossa loja on-line. Queremos remover o cliente do nosso back-end, então tentamos a seguinte operação.

DELETE FROM Customers WHERE CustomerID = 721;

Neste exemplo, o Cloud Spanner detecta, usando a restrição de chave externa, que ainda há registros na tabela Orders que fazem referência à linha do cliente que estamos tentando excluir. O erro a seguir é exibido nesse caso.

Foreign key constraint violation when deleting or updating referenced row(s): referencing row(s) found in table `Orders`.

Para corrigir esse problema, excluímos todas as entradas de referência em Orders primeiro. Outra solução, se definimos o CustomerID como NULLABLE, seria definir o campo CustomerID como NULL para remover a referência. CASCADE DELETE não é compatível com chaves externas.

Como visualizar propriedades de uma relação de chave estrangeira

A INFORMATION_SCHEMA do Cloud Spanner contém informações sobre chaves externas e os respectivos índices de backup. Veja a seguir alguns exemplos das perguntas que você pode responder consultando a INFORMATION SCHEMA.

Para mais informações sobre índices de backup, consulte Índices de backup de chaves externas.

Quais restrições são definidas no meu banco de dados?

SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = "FOREIGN KEY";

Quais chaves externas são definidas no meu banco de dados?

SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.SPANNER_STATE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;

Quais índices são secundários para chaves estrangeiras, também conhecidos como índices de backup?

Os índices de backup de chave externa são gerenciados pelo Cloud Spanner. Portanto, consultar SPANNER_IS_MANAGED na visualização INDEXES retornará todos os índices de backup.

SELECT i.TABLE_NAME, i.INDEX_NAME, i.INDEX_TYPE, i.INDEX_STATE,
  i.IS_UNIQUE, i.IS_NULL_FILTERED, i.SPANNER_IS_MANAGED
FROM INFORMATION_SCHEMA.INDEXES as i
WHERE SPANNER_IS_MANAGED is true;

Para mais informações, consulte Esquema de informações.

Como remover uma relação de chave estrangeira

A DDL a seguir remove uma restrição de chave externa da tabela Orders.

ALTER TABLE Orders
  DROP CONSTRAINT FK_CustomerOrder;

Os índices de backup de chave externa são descartados automaticamente quando a própria restrição é descartada.

Suporte para relacionamentos mais complexos de chaves estrangeiras

Várias colunas

As chaves externas podem fazer referência a várias colunas. A lista de colunas forma uma chave que corresponde à chave primária de uma tabela ou a um índice de backup. A tabela de referência contém chaves externas da chave de tabela referenciada.

No exemplo a seguir, as definições de chave externa informam ao Cloud Spanner que cada valor SongName na tabela TopHits precisa ter um valor correspondente na tabela de "Songs". E cada par de valores SingerFirstName e SingerLastName precisa ter um par de valores FirstName e LastName correspondentes na tabela "Singers".

CREATE TABLE TopHits (
  Rank INT64 NOT NULL,
  SongName STRING(MAX),
  SingerFirstName STRING(MAX),
  SingerLastName STRING(MAX),

  -- Song names must either be NULL or have matching values in Songs.
  FOREIGN KEY (SongName) REFERENCES Songs (SongName),

  -- Singer names must either be NULL or have matching values in Singers.
  FOREIGN KEY (SingerFirstName, SingerLastName)
  REFERENCES Singers (FirstName, LastName)

) PRIMARY KEY (Rank);

Referências circulares

Ocasionalmente, as tabelas têm dependências circulares, talvez por motivos legados ou devido à desnormalização. As chaves externas do Cloud Spanner permitem referências circulares. Como uma tabela referenciada precisa existir antes que uma chave externa possa referenciá-la, uma das chaves externas precisa ser adicionada com uma instrução ALTER TABLE. Veja um exemplo:

1. Criar a tabela A, sem uma chave externa
2. Criar a tabela B com uma restrição de chave externa na tabela A.
3. Usar ALTER TABLE na tabela A para criar uma referência de chave externa para a tabela B.

Tabelas de autorreferência

Um tipo especial de referência circular é uma tabela que define uma chave externa que faz referência à mesma tabela. Por exemplo, o snippet a seguir mostra uma chave externa para determinar que o ManagerId de um funcionário também seja um funcionário.

CREATE TABLE Employees (
  EmployeeId INT64 NOT NULL,
  EmployeeName STRING(MAX) NOT NULL,
  ManagerId INT64,
  FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId)
) PRIMARY KEY (EmployeeId);

A seguir

• Saiba mais sobre o suporte a chaves externas no Cloud Spanner.

• Saiba mais sobre o INFORMATION SCHEMA do Cloud Spanner.