Criar e gerenciar relacionamentos de chave estrangeira

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 de banco de dados simples em que os dados de uma tabela têm uma relação com os dados de outra.

Exemplo de esquema de banco de dados mostrando relações de chave externa entre tabelas.

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.

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

Adicionar uma chave estrangeira ao criar uma nova tabela

Vamos supor que você tenha criado uma tabela Customers no seu banco de dados simples 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, não permita que o sistema insira linhas na tabela Orders, a menos que haja também uma entrada correspondente na tabela Customers. Portanto, você precisa de uma chave externa para estabelecer uma relação entre as duas tabelas. Uma opção é adicionar uma coluna CustomerID à nova tabela e usá-la como a chave externa para criar uma relação com a coluna CustomerID na tabela Customers.

Ao criar uma nova tabela com uma chave externa, você usa 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 referencia 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 com as seguintes características:

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

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

  • 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 falhará.

O exemplo 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 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.

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 existentes. Primeiro, você precisa descartar a restrição atual. Em seguida, use ALTER TABLE para adicionar outra restrição de chave externa à tabela Orders, conforme mostrado abaixo:

ALTER TABLE Orders
  ADD CONSTRAINT DB_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 Spanner nomeando 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 ao criar 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, você quer usar a ação ON DELETE CASCADE com a restrição de chave externa.

A instrução DDL CREATE TABLE a seguir 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.