Criar e gerenciar relacionamentos de chave externa

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

Uma chave externa é uma coluna compartilhada entre tabelas para estabelecer um vínculo entre dados relacionados. Quando você usa uma chave externa, o Spanner garante que essa relação seja mantida.

O diagrama a seguir mostra um esquema básico de banco de dados em que os dados de uma tabela têm uma relação com os dados de outra tabela.

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 as tabelas Orders e Products garante que um pedido não possa ser criado para um produto que não existe.

Usando o esquema anterior como exemplo, este tópico discute as instruções da linguagem de definição de dados (DDL) CONSTRAINT que podem ser usadas para gerenciar relações entre tabelas em um banco de dados.

Por padrão, todas as chaves externas no Spanner são chaves externas aplicadas, que aplicam a integridade referencial. No Spanner, também é possível usar chaves externas informativas, que não validam nem aplicam a integridade referencial. Para mais informações, consulte Comparação de chaves externas e Escolher o tipo de chave externa a ser usado. Quando não especificadas, as chaves externas nos exemplos desta página são chaves externas aplicadas.

Adicionar uma chave externa a uma nova tabela

Suponha que você tenha criado uma tabela Customers no banco de dados básico de pedidos de produtos. Agora você precisa de uma tabela Orders para armazenar informações sobre os pedidos que os clientes fazem. Para garantir que todos os pedidos sejam válidos, você não quer que o sistema insira linhas na tabela Orders, a menos que haja uma entrada correspondente na tabela Customers. Portanto, é preciso uma chave externa aplicada para estabelecer uma relação entre as duas tabelas. Uma opção é adicionar uma coluna CustomerID à nova tabela e usá-la como chave externa para criar uma relação com a coluna CustomerID na tabela Customers.

Ao criar uma nova tabela com uma chave externa, use REFERENCE para estabelecer uma relação com outra tabela. A tabela que contém a instrução REFERENCE é chamada de tabela de referência. A tabela nomeada na instrução REFERENCE é a tabela referenciada. A coluna nomeada na instrução REFERENCE é chamada de coluna de referência.

O exemplo a seguir mostra como usar a instrução DDL CREATE TABLE para criar a tabela Orders com uma restrição de chave externa que faz referência a CustomerID na tabela Customers.

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

A instrução anterior contém uma cláusula CONSTRAINT que tem as seguintes características:

• Uso da sintaxe CONSTRAINT para nomear uma restrição, facilitando a remoção da tabela usando o nome escolhido.

• A restrição tem o nome FK_CustomerOrder. Os nomes de restrição têm escopo no esquema e precisam ser exclusivos.

• A tabela Orders, em que você define a restrição, é a 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 vai 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 exemplo a seguir mostra uma instrução alternativa de criação de tabela. Aqui, a restrição de chave externa é definida sem um nome. Quando você usa essa sintaxe, o Spanner gera um nome para você. Para descobrir os nomes de todas as chaves externas, consulte Ver propriedades de uma relação de chave externa.

GoogleSQL

CREATE TABLE Orders (
OrderID INT64 NOT NULL,
CustomerID INT64 NOT NULL,
ProductID INT64 NOT NULL,
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,
FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
PRIMARY KEY (OrderID)
);

Adicionar uma chave estrangeira a uma tabela atual

Você também quer garantir que os clientes só possam pedir produtos que existem. Se a tabela tiver restrições, você precisará eliminar todas elas. No Spanner, todas as restrições aplicadas em uma tabela precisam ser implementadas ao mesmo tempo em uma única instrução DDL em lote.

Se a tabela não tiver restrições, use a instrução DDL ALTER TABLE para adicionar uma restrição de chave externa aplicada à tabela Orders, conforme mostrado no exemplo a seguir:

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

A coluna de referência em Orders é ProductID, que faz referência à coluna ProductID em Products. Se você não se importar que o Spanner nomeie essas restrições para você, use a seguinte sintaxe:

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

Adicionar uma chave externa com uma ação de exclusão a uma nova tabela

Lembre-se do exemplo anterior em que você tem uma tabela Customers em um banco de dados de pedidos de produtos que precisa de uma tabela Orders. Você quer adicionar uma restrição de chave externa que faça referência à tabela Customers. No entanto, você quer garantir que, ao excluir um registro de cliente no futuro, o Spanner também exclua todos os pedidos desse cliente. Nesse caso, use a ação ON DELETE CASCADE com a restrição de chave externa.

A seguinte instrução DDL CREATE TABLE para a tabela Orders inclui a restrição de chave externa que faz referência à tabela Customers com uma ação ON DELETE CASCADE.

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) ON DELETE CASCADE
) PRIMARY KEY (OrderID);

PostgreSQL

CREATE TABLE Orders (
OrderID BIGINT NOT NULL,
CustomerID BIGINT NOT NULL,
Quantity BIGINT NOT NULL,
ProductID BIGINT NOT NULL,
FOREIGN KEY (CustomerID)
  REFERENCES Customers (CustomerID) ON DELETE CASCADE,
PRIMARY KEY (OrderID)
);

A instrução anterior contém uma restrição de chave externa com uma cláusula ON DELETE CASCADE. A coluna CustomerID é uma chave externa que faz referência ao campo CustomerID na tabela Customers. Isso significa que cada valor CustomerID na tabela Orders também precisa existir na tabela Customers. Se alguém tentar excluir uma linha da tabela Customers, todas as linhas da tabela Orders que fazem referência ao valor CustomerID excluído também serão excluídas na mesma transação.

Da mesma forma, é possível usar a ação ON DELETE SET NULL para definir as colunas de referência como NULL em vez de excluir as linhas de referência. Para usar ON DELETE SET NULL, as colunas de referência precisam ser anuláveis (ou seja, não marcadas como NOT NULL). Elas não podem fazer parte da chave primária da tabela de referência ou das colunas geradas.

Para detalhes sobre como ON DELETE SET NULL e ON DELETE CASCADE se comparam em mutações, leituras e comportamento em cascata, consulte Uso de recursos para ações de exclusão de chave externa.

A seguinte instrução DDL CREATE TABLE para a tabela Orders inclui uma restrição de chave externa que faz referência à tabela Customers com uma ação ON DELETE SET NULL. Neste exemplo, CustomerID na tabela Orders pode ser nulo.

GoogleSQL

CREATE TABLE Orders (
OrderID INT64 NOT NULL,
CustomerID INT64,
Quantity INT64 NOT NULL,
ProductID INT64 NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID)
  REFERENCES Customers (CustomerID) ON DELETE SET NULL
) PRIMARY KEY (OrderID);

PostgreSQL

CREATE TABLE Orders (
OrderID BIGINT NOT NULL,
CustomerID BIGINT,
Quantity BIGINT NOT NULL,
ProductID BIGINT NOT NULL,
CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID)
  REFERENCES Customers (CustomerID) ON DELETE SET NULL,
PRIMARY KEY (OrderID)
);

Observação: se você não especificar uma ação de chave externa, a ação padrão será NO ACTION. Isso significa que, se alguém tentar excluir uma linha da tabela Customers e houver linhas na tabela Orders que façam referência ao valor CustomerID excluído, a operação de exclusão falhará com um erro de violação de restrição de chave externa externa.

Adicionar uma chave externa com uma ação de exclusão a uma tabela

Você também quer garantir que os pedidos sejam criados apenas para produtos que existem. Use ALTER TABLE para adicionar outra restrição de chave externa com a ação ON DELETE CASCADE à tabela de pedidos da seguinte maneira:

ALTER TABLE Orders
  ADD CONSTRAINT DB_ProductOrder FOREIGN KEY (ProductID)
    REFERENCES Products (ProductID) ON DELETE CASCADE;

Excluir uma linha da tabela Products exclui todas as linhas da tabela Orders que fazem referência ao valor ProductID excluído. Também é possível usar ON DELETE SET NULL em uma instrução ALTER TABLE se a coluna de referência for anulável, não fizer parte da chave primária e não for uma coluna gerada.

Observação: não é possível adicionar ou remover uma ação de chave externa em uma restrição de chave externa atual. Você precisa adicionar uma nova restrição de chave externa com uma ação. Para mais informações, consulte Alterações de esquema de longa duração.

Usar chaves externas informativas (somente GoogleSQL)

As chaves externas informativas permitem que o otimizador de consultas use a relação de chave externa sem a sobrecarga gerada pelas verificações de integridade referencial realizadas por chaves externas aplicadas. As chaves externas informativas são úteis quando a aplicação de uma integridade referencial estrita é impraticável ou gera uma sobrecarga de desempenho significativa.

Continuando com o exemplo anterior, imagine que você quer modelar as relações entre as tabelas Customers, Orders e Products. No entanto, aplicar uma integridade referencial estrita nos dados das tabelas pode introduzir gargalos de desempenho, especialmente durante períodos de pico de compras com altos volumes de pedidos. Além disso, os clientes podem fazer pedidos de produtos que foram desativados e removidos da tabela Products.

É possível criar a tabela Orders usando chaves externas informativas:

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,
    CONSTRAINT FK_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID) NOT ENFORCED
) PRIMARY KEY (OrderID);

Ao criar uma chave externa informativa com NOT ENFORCED, você permite que um pedido faça referência a um cliente ou produto inexistente. Usar uma chave externa informativa em vez de uma restrição de chave externa aplicada é uma boa opção se uma conta de cliente puder ser excluída ou um produto puder ser descontinuado. Com uma chave externa informativa, o Spanner não realiza a validação de integridade referencial. Isso reduz a sobrecarga de gravação, o que pode melhorar o desempenho durante os horários de pico de processamento de pedidos.

É possível permitir que o otimizador de consultas use as relações para gerar planos de consulta eficientes. Isso pode melhorar o desempenho das consultas que mesclam as tabelas em colunas de chave externa. Para mais informações, consulte chave externa informativa para otimização de consultas.

Cuidado: se a lógica do aplicativo exigir integridade referencial estrita (por exemplo, impedir pedidos de produtos que não existem), será necessário implementar verificações adicionais no nível do aplicativo. Os resultados da consulta podem ser afetados se houver inconsistências nos dados. Por exemplo, uma consulta que une Orders com Products pode retornar um pedido com um ProductID desativado se o otimizador usar uma chave externa informativa.

Consultar dados em relações de chave externa

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

Integridade referencial com chaves externas aplicadas

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

Considere os dados na Figura 2. Alguns clientes pediram produtos, como mostrado na tabela Orders. Devido à restrição de chave externa aplicada, os dados inseridos na tabela Orders têm integridade referencial.

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

Os exemplos a seguir mostram o que acontece quando você tenta modificar os dados de uma maneira que quebra a integridade referencial.

• Adicione uma linha à tabela Orders com um valor CustomerID que não existe em Customers.

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

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

  Nesse caso, o sistema tentaria inserir uma linha em Orders com um CustomerID (447) que não existe na tabela Customers. Se o sistema fizesse isso, você teria um pedido inválido no sistema. No entanto, com a restrição de chave externa aplicada que você adicionou à tabela Orders, ela está protegida. O INSERT falha com a seguinte mensagem, supondo que a restrição seja chamada de FK_CustomerOrder.

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

  Ao contrário das chaves externas aplicadas, as chaves externas informativas não aplicam a integridade referencial. Se FK_CustomerOrder for uma chave externa informativa, a instrução de inserção será bem-sucedida porque o Spanner não valida se o CustomerID correspondente existe na tabela Customers. Por isso, os dados podem não estar em conformidade com a integridade referencial definida por FK_CustomerOrder.

• Tente excluir uma linha da tabela Customers quando o cliente for referenciado em uma restrição de chave externa aplicada.

  Imagine uma situação em que um cliente cancela a inscrição na sua loja on-line. Você quer remover o cliente do back-end, então tenta a seguinte operação.

  DELETE FROM Customers WHERE CustomerID = 721;
  

  Neste exemplo, o Spanner detecta, por meio da restrição de chave externa, que ainda há registros na tabela Orders que fazem referência à linha do cliente que você está tentando excluir. O seguinte erro é 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, exclua primeiro todas as entradas de referência em Orders. Também é possível definir a chave externa com a ação ON DELETE CASCADE para permitir que o Spanner processe a exclusão de entradas de referência.

  Da mesma forma, se FK_CustomerOrder for uma chave externa informativa, a ação de exclusão será bem-sucedida porque o Spanner não garante a integridade referencial de chaves externas informativas.

Ver propriedades de um relacionamento de chave estrangeira

O INFORMATION_SCHEMA do Spanner contém informações sobre chaves externas e os índices de aplicação delas. Veja alguns exemplos de perguntas que podem ser respondidas com uma consulta ao INFORMATION SCHEMA.

Para mais informações sobre índices de aplicação, consulte Índices de aplicação 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 externas, também conhecidos como índices de aplicação?

Os índices de aplicação de chave externa são gerenciados pelo Spanner. Portanto , consultar SPANNER_IS_MANAGED na visualização INDEXES retorna todos os índices de aplicação.

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 = 'YES';

Qual é a ação referencial definida com a restrição de chave externa?

SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.DELETE_RULE,
  rc.UPDATE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;

Uma chave externa é aplicada ou não aplicada?

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

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

Remover um relacionamento 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 aplicação de chave externa são descartados automaticamente quando a própria restrição é descartada.

Suporte para relacionamentos mais complexos de chaves estrangeiras

Os tópicos a seguir mostram como usar chaves externas para aplicar relações mais complexas entre tabelas.

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 aplicação. A tabela de referência contém chaves externas da chave de tabela referenciada.

No exemplo a seguir, as definições de chave externa aplicadas indicam que:

• Cada valor SongName na tabela TopHits precisa ter um valor correspondente na tabela Songs.

• Cada par de valores SingerFirstName e SingerLastName precisa ter um par de valores FirstName e LastName correspondente na tabela Singers.

GoogleSQL

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

PostgreSQL

CREATE TABLE TopHits (
Rank BIGINT NOT NULL,
SongName VARCHAR,
SingerFirstName VARCHAR,
SingerLastName VARCHAR,

-- 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 Spanner permitem referências circulares. Como uma tabela referenciada precisa existir antes que uma chave externa possa fazer referência a ela, uma das chaves externas precisa ser adicionada com uma instrução ALTER TABLE. Veja um exemplo:

1. Crie TableA sem uma chave externa.
2. Crie TableB com uma restrição de chave externa em TableA.
3. Use ALTER TABLE em TableA para criar uma referência de chave externa para TableB.

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 garantir que o ManagerId de um funcionário também seja um funcionário.

GoogleSQL

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

PostgreSQL

CREATE TABLE Employees (
EmployeeId BIGINT NOT NULL,
EmployeeName VARCHAR NOT NULL,
ManagerId BIGINT,
FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId),
PRIMARY KEY (EmployeeId)
);

A seguir

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

• Saiba mais sobre o INFORMATION SCHEMA do Spanner.