Nesta página, descrevemos como gerenciar relações de chave externa no banco de dados.
Uma chave externa é uma coluna que é compartilhada entre tabelas para estabelecer uma 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 em 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 tabelaCustomers
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 tabelaProducts
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 aborda os conceitos
Instruções CONSTRAINT
de linguagem de definição (DDL) que podem ser usadas para gerenciar
relações entre tabelas em um banco de dados.
Adicionar uma chave estrangeira ao criar uma nova tabela
Suponha que você tenha criado uma tabela Customers
no seu produto simples
no banco de dados de pedidos. Agora você precisa de uma tabela Orders
para armazenar informações sobre a
pedidos que os clientes fazem. Para garantir que todos os pedidos sejam válidos, não
deixa o sistema inserir linhas na tabela Orders
, a menos que também haja uma
entrada correspondente na tabela Customers
. Portanto, você precisa de uma chave externa para
para estabelecer uma relação entre as duas tabelas. Uma opção é adicionar
CustomerID
para a nova tabela e usá-la como a chave externa para criar
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
a tabela nomeada na instrução REFERENCE
é a tabela referenciada. A coluna
que tem o nome na instrução REFERENCE
é chamada de coluna de referência.
O exemplo a seguir mostra como usar a instrução DDL CREATE TABLE
para
crie a tabela Orders
com uma restrição de chave externa que faça referência
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 o seguinte
características:
O uso da sintaxe
CONSTRAINT
para nomear uma restrição, tornando-a será mais fácil descartar a tabela usando o nome escolhido.A restrição tem o nome
FK_CustomerOrder
. Os nomes das restrições têm como escopo no esquema e precisa ser único dentro dele.A tabela
Orders
, em que você define a restrição, é a tabela de referência. A tabelaCustomers
é a tabela referenciada.A coluna de referência na tabela de referência é
CustomerID
. Ela faz referência ao campoCustomerID
na tabelaCustomers
. Se alguém tentar para inserir uma linha emOrders
com umCustomerID
que não existe emCustomers
, a inserção falhará.
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 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 comprar produtos
existem. Primeiro, elimine a restrição atual. Em seguida, use
ALTER TABLE
para adicionar outra restrição de chave externa à tabela Orders
, conforme
mostrados a seguir:
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ê não quiser que o Spanner nomeie esses
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 há uma tabela Customers
em um produto
banco de dados de pedidos que precisa de uma tabela Orders
. Você deseja adicionar uma chave externa
que faz referência à tabela Customers
. No entanto, convém garantir
quando um registro de cliente for excluído no futuro, o Spanner
também exclui todos os pedidos desse cliente. Nesse caso, você quer usar
Ação ON DELETE CASCADE
com a restrição de chave externa.
A seguinte instrução DDL CREATE TABLE
para a tabela Orders
inclui o
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 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
O valor CustomerID
na tabela Orders
também precisa existir no objeto Customers
.
tabela. Se alguém tentar excluir uma linha da tabela Customers
, todas as
As linhas na tabela Orders
que fazem referência ao valor CustomerID
excluído são
sejam excluídos na mesma transação.
Adicionar uma chave externa com uma ação de exclusão a uma tabela existente
Você também quer garantir que os pedidos sejam criados somente para produtos que
existem. É possível usar ALTER TABLE
para adicionar outra restrição de chave externa com
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 faz referência ao valor ProductID
excluído.
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;
Exemplos de como o Spanner mantém a integridade referencial
O principal motivo para adicionar relacionamentos de chave externa é para que o Spanner possa manter integridade referencial de seus dados. Se você modificar os dados de forma a quebrar uma restrição de chave externa, a atualização vai falhar com um erro.
Considere os dados da Figura 2. Alguns clientes solicitaram produtos, conforme mostrado na tabela de pedidos. Por causa das chaves estrangeiras disponíveis, você
pode garantir que os dados inseridos na tabela Orders
tenham
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 em uma isso corromperia a integridade referencial.
Adicione uma linha à tabela
Orders
com um valorCustomerID
que não existem no seguinte país:Customers
O que acontece se você tentar a modificação a seguir, com base nos 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 na
Orders
com umaCustomerID
(447) que não existe na tabelaCustomers
. Se o sistema fez isso, teria um pedido inválido no seu sistema. No entanto, com o restrição de chave externa adicionada à tabelaOrders
, ela será protegidas. OINSERT
falha com a mensagem a seguir, supondo que o é chamadaFK_CustomerOrder
.Foreign key constraint `FK_CustomerOrder` is violated on table `Orders`. Cannot find referenced values in Customers(CustomerID).
Tente excluir uma linha da tabela
Customers
quando o cliente estiver referenciada em uma restrição de chave externa.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 o seguinte operação
DELETE FROM Customers WHERE CustomerID = 721;
Neste exemplo, o Spanner detecta pela restrição de chave externa se ainda há registros na tabela
Orders
que fazem referência ao cliente de linha que você está 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, primeiro exclua todas as entradas de referência em
Orders
. Você também pode definir a chave externa com a açãoON DELETE CASCADE
para permitir O Spanner processa a exclusão de entradas de referência.
Ver propriedades de um relacionamento de chave estrangeira
O INFORMATION_SCHEMA do Spanner contém informações sobre chaves externas e os índices de apoio. 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 apoio de chave externa são gerenciados pelo Spanner , portanto, consultar por
SPANNER_IS_MANAGED
na visualização INDEXES
retorna 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;
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 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 Spanner
que cada valor SongName
na tabela "TopHits" precisa ter um valor correspondente em
a tabela Músicas; e cada par de valores SingerFirstName
e SingerLastName
precisa ter um par de valores FirstName
e LastName
correspondentes no campo Singers
tabela.
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 referenciá-la, uma das chaves externas precisa ser adicionada com uma instrução ALTER TABLE
. Veja um exemplo:
- Criar a tabela A, sem uma chave externa
- Criar a tabela B com uma restrição de chave externa na tabela A.
- 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.
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 estrangeiras no Spanner.
Saiba mais sobre o INFORMATION SCHEMA do Spanner.