Crie e faça a gestão de relações de chaves externas

Esta página descreve como gerir relações de chaves externas na sua base de dados.

Uma chave externa é uma coluna partilhada entre tabelas para estabelecer uma ligação entre dados relacionados. Quando usa uma chave externa, o Spanner garante que esta relação é mantida.

O diagrama seguinte mostra um esquema de base de dados básico em que os dados numa tabela têm uma relação com os dados noutra tabela.

Figura 1. Diagrama de um esquema de base de dados de processamento de encomendas

Existem três tabelas no esquema apresentado na Figura 1:

• A tabela Customers regista os nomes de cada cliente.
• As tabelas Orders monitorizam todas as encomendas feitas.
• A tabela Products armazena as informações dos produtos de cada produto.

Existem duas relações de chaves externas entre estas tabelas:

• É definida uma relação de chave externa entre a tabela Orders e a tabela Customers para garantir que não é possível criar uma encomenda, a menos que exista um cliente correspondente.

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

Usando o esquema anterior como exemplo, este tópico aborda as declarações da linguagem de definição de dados (DDL) CONSTRAINT que pode usar para gerir as relações entre tabelas numa base de dados.

Por predefinição, todas as chaves externas no Spanner são chaves externas aplicadas, que aplicam a integridade referencial. No Spanner, também pode optar por usar chaves estrangeiras informativas, que não validam nem aplicam a integridade referencial. Para mais informações, consulte os artigos Comparação de chaves externas e Escolha o tipo de chave externa a usar. Quando não especificado, as chaves estrangeiras nos exemplos desta página são chaves estrangeiras aplicadas.

Adicione uma chave externa a uma nova tabela

Suponha que criou uma tabela Customers na sua base de dados de encomendas de produtos básica. Agora, precisa de uma tabela Orders para armazenar informações sobre as encomendas que os clientes fazem. Para garantir que todas as encomendas são válidas, não quer permitir que o sistema insira linhas na tabela Orders, a menos que também exista uma entrada correspondente na tabela Customers. Por conseguinte, precisa de 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.

Quando cria uma nova tabela com uma chave externa, usa REFERENCE para estabelecer uma relação com outra tabela. A tabela que contém a declaração REFERENCE é denominada tabela de referência. A tabela nomeada na declaração REFERENCE é a tabela referenciada. A coluna com o nome indicado na declaração REFERENCE é denominada coluna de referência.

O exemplo seguinte mostra como usar a declaraçã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 declaração anterior contém uma cláusula CONSTRAINT com as seguintes características:

• Usar a sintaxe CONSTRAINT para atribuir um nome a uma restrição, o que facilita a eliminação da tabela com o nome que escolheu.

• A restrição tem o nome FK_CustomerOrder. Os nomes das restrições estão no âmbito do esquema e têm de ser exclusivos no esquema.

• A tabela Orders, na qual 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. 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 falha.

Nota: frequentemente, as colunas referenciadas da tabela referenciada correspondem às colunas da chave primária, mas isto 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 seguinte mostra uma declaração de criação de tabela alternativa. Aqui, a restrição de chave externa é definida sem um nome. Quando usa esta sintaxe, o Spanner gera um nome para si. Para descobrir os nomes de todas as chaves externas, consulte o artigo 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)
);

Adicione uma chave externa a uma tabela existente

Também quer certificar-se de que os clientes só podem encomendar produtos existentes. Se a sua tabela tiver restrições existentes, tem de eliminar todas as restrições. No Spanner, todas as restrições aplicadas numa tabela têm de ser implementadas em simultâneo numa única declaração DDL em lote.

Se a sua tabela não tiver restrições existentes, pode usar a declaração ALTER TABLE DDL para adicionar uma restrição de chave externa aplicada à tabela Orders existente, conforme mostrado no exemplo seguinte:

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 Products. Se não tiver problemas em que o Spanner nomeie estas restrições por si, use a seguinte sintaxe:

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

Adicione uma chave externa com uma ação de eliminação a uma nova tabela

Recorde o exemplo anterior em que tem uma tabela Customers numa base de dados de encomendas de produtos que precisa de uma tabela Orders. Quer adicionar uma restrição de chave externa que faça referência à tabela Customers. No entanto, quer garantir que, quando eliminar um registo de cliente no futuro, o Spanner também elimina todas as encomendas desse cliente. Neste caso, quer usar a ação ON DELETE CASCADE com a restrição de chave externa.

A seguinte declaraçã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 declaraçã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. Isto significa que cada valor CustomerID na tabela Orders também tem de existir na tabela Customers. Se alguém tentar eliminar uma linha da tabela Customers, todas as linhas na tabela Orders que fazem referência ao valor CustomerID eliminado também são eliminadas na mesma transação.

Nota: se não especificar uma ação de chave externa, a ação predefinida é NO ACTION. Isto significa que, se alguém tentar eliminar uma linha da tabela Customers e existirem linhas na tabela Orders que referenciem o valor CustomerID eliminado, a operação de eliminação falha com um erro de violação da restrição de chave externa.

Adicione uma chave externa com uma ação de eliminação a uma tabela

Também quer certificar-se de que as encomendas só são criadas para produtos que existem. Pode usar ALTER TABLE para adicionar outra restrição de chave externa com a ação ON DELETE CASCADE à tabela de encomendas da seguinte forma:

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

Se eliminar uma linha da tabela Products, elimina todas as linhas da tabela Orders que fazem referência ao valor ProductID eliminado.

Nota: adicionar ou remover uma ação de chave externa numa restrição de chave externa existente não é suportado. Tem de adicionar uma nova restrição de chave externa com uma ação. Para mais informações, consulte o artigo Alterações ao esquema de longa duração.

Use chaves estrangeiras informativas (apenas no GoogleSQL)

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

Continuando com o exemplo anterior, imagine que quer modelar as relações entre as tabelas Customers, Orders e Products. No entanto, a aplicação de uma integridade referencial rigorosa nos dados das tabelas pode introduzir restrições de desempenho, especialmente durante os períodos de compras de pico com volumes de encomendas elevados. Além disso, os clientes podem fazer encomendas de produtos que foram descontinuados e removidos da tabela Products.

Pode criar a tabela Orders com 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, permite a possibilidade de uma encomenda poder fazer referência a um cliente ou um 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 eliminada ou um produto puder ser descontinuado. Com uma chave externa informativa, o Spanner não realiza a validação da integridade referencial. Isto reduz a sobrecarga de escrita, o que pode melhorar o desempenho durante os horários de pico de processamento de encomendas.

Pode permitir que o otimizador de consultas use as relações para gerar planos de consultas eficientes. Isto pode melhorar o desempenho das consultas que unem as tabelas nas colunas de chaves externas. Para mais informações, consulte o artigo Chave externa informativa para otimização de consultas.

Aviso: se a lógica da sua aplicação exigir uma integridade referencial rigorosa (por exemplo, impedir encomendas de produtos que não existem), tem de implementar verificações adicionais ao nível da aplicação. Os resultados da consulta podem ser afetados se existirem inconsistências nos dados. Por exemplo, uma consulta que junta Orders com Products pode devolver uma encomenda com um produto descontinuado ProductID se o otimizador usar uma chave externa informativa.

Consultar dados em relações de chaves externas

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 chaves externas aplicadas é que o Spanner possa manter a integridade referencial dos seus dados. Se modificar os dados de forma a violar uma restrição de chave externa, a atualização falha com um erro.

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

Figura 2. Dados de amostra na nossa base de dados de encomendas.

Os exemplos seguintes mostram o que acontece quando tenta modificar os dados de uma forma que prejudica a integridade referencial.

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

  O que acontece se tentar a seguinte modificação, tendo em conta os dados de exemplo do diagrama anterior?

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

  Neste caso, o sistema tentaria inserir uma linha em Orders com um CustomerID (447) que não existe na tabela Customers. Se o sistema o fizesse, teria uma encomenda inválida no seu sistema. No entanto, com a restrição de chave externa aplicada que adicionou à tabela Orders, a tabela está protegida. O INSERT falha com a seguinte mensagem, partindo do princípio de que a restrição se chama 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 estrangeira informativa, a declaração de inserção é bem-sucedida porque o Spanner não valida se o CustomerID correspondente existe na tabela Customers. Por este motivo, os dados podem não estar em conformidade com a integridade referencial definida por FK_CustomerOrder.

• Tentar eliminar uma linha da tabela Customers quando o cliente é referenciado numa restrição de chave externa aplicada.

  Imagine uma situação em que um cliente anula a subscrição da sua loja online. Quer remover o cliente do seu back-end, pelo que tenta a seguinte operação.

  DELETE FROM Customers WHERE CustomerID = 721;
  

  Neste exemplo, o Spanner deteta através da restrição de chave externa que ainda existem registos na tabela Orders que fazem referência à linha do cliente que está a tentar eliminar. Neste caso, é apresentado o seguinte erro.

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

  Para corrigir este problema, elimine primeiro todas as entradas de referência em Orders. Também pode definir a chave externa com a ação ON DELETE CASCADE para permitir que o Spanner processe a eliminação de entradas de referência.

  Da mesma forma, se FK_CustomerOrder for uma chave estrangeira informativa, a ação de eliminação é bem-sucedida porque o Spanner não garante a integridade referencial das chaves estrangeiras informativas.

Veja as propriedades de uma relação de chave externa

O INFORMATION_SCHEMA do Spanner contém informações sobre chaves externas e os respetivos índices de apoio. Seguem-se alguns exemplos de perguntas às quais pode responder consultando o INFORMATION SCHEMA.

Para mais informações sobre a cópia de segurança de índices, consulte o artigo Índices de cópia de segurança de chaves estrangeiras.

Que restrições estão definidas na minha base 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';

Que chaves externas estão definidas na minha base de dados?

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

Que índices são índices secundários para chaves externas, também conhecidos como índices de apoio?

Os índices de apoio das chaves externas são geridos pelo Spanner , pelo que a consulta de SPANNER_IS_MANAGED na vista INDEXES devolve todos os índices de apoio.

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 o artigo Esquema de informações.

Remova uma relação de chave externa

O DDL seguinte elimina uma restrição de chave externa da tabela Orders.

ALTER TABLE Orders
  DROP CONSTRAINT FK_CustomerOrder;

Os índices de apoio das chaves externas são eliminados automaticamente quando a própria restrição é eliminada.

Suporte para relações de chaves externas mais complexas

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

Várias colunas

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

No exemplo seguinte, as definições de chaves externas aplicadas indicam que:

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

• Cada par de valores SingerFirstName e SingerLastName tem de 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 de herança ou devido à desnormalização. As chaves externas do Spanner permitem referências circulares. Uma vez que uma tabela referenciada tem de existir antes de uma chave externa a poder referenciar, uma das chaves externas tem de ser adicionada com uma declaração ALTER TABLE. Segue-se um exemplo

1. Crie TableA sem uma chave estrangeira.
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 a 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 fragmento seguinte mostra uma chave externa para garantir que o ManagerId de um funcionário também é 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)
);

O que se segue?

• Saiba mais sobre o suporte de chaves estrangeiras no Spanner.

• Saiba mais acerca do INFORMATION SCHEMA do Spanner.