Menu

Chave estrangeira no PostgreSQL e ON DELETE CASCADE

Uma chave estrangeira faz uma coluna aceitar só valores que existem em outra tabela. Inclui REFERENCES, ON DELETE CASCADE, SET NULL e RESTRICT, como adicionar a chave a uma tabela existente e como indexá-la.

Esta página tem editores executáveis. Edite, execute e veja a saída na hora.

Uma chave estrangeira liga uma coluna à chave primária, ou a uma coluna única, de outra tabela. O PostgreSQL rejeita qualquer valor que não tenha uma linha correspondente lá. Escreva como column type REFERENCES other_table (column). Adicione ON DELETE CASCADE ou ON DELETE SET NULL para dizer o que acontece com as linhas filhas quando o pai é apagado.

A coluna referenciada precisa ter uma restrição PRIMARY KEY ou UNIQUE. Com REFERENCES customers sem coluna, o PostgreSQL usa a chave primária de customers.

Como é uma violação

Um pedido para um cliente que não existe é recusado:

O erro cita a restrição, orders_customer_id_fkey, que é o nome que o PostgreSQL dá a uma chave estrangeira sem nome. Apagar um cliente que ainda tem pedidos falha do mesmo jeito, a não ser que a chave diga outra coisa.

Opções de ON DELETE

OpçãoQuando a linha pai é apagada
NO ACTION (padrão)A exclusão falha enquanto existirem linhas filhas
RESTRICTIgual, mas a verificação não pode ser adiada para o fim da transação
CASCADEAs linhas filhas também são apagadas
SET NULLA coluna da chave estrangeira na filha vira NULL
SET DEFAULTA coluna da filha recebe o valor padrão

ON UPDATE aceita as mesmas opções. Ele vale quando o valor da chave do pai muda, o que é raro com ids gerados.

CASCADE combina com linhas que não fazem sentido sem o pai, como os itens de um pedido:

Só sobra chair. As cascatas seguem outras chaves, então uma única exclusão pode remover linhas em várias tabelas.

SET NULL mantém a linha filha e a desvincula:

Ada continua, com team_id igual a NULL. Desde o PostgreSQL 15, ON DELETE SET NULL (column) pode limpar só algumas colunas de uma chave com várias colunas.

Adicionar uma chave estrangeira a uma tabela existente

ALTER TABLE orders
    ADD CONSTRAINT orders_customer_id_fkey
    FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;

Isso verifica todas as linhas existentes e falha se alguma não tiver pai. Numa tabela grande, adicione a chave com NOT VALID, que verifica só as linhas novas. Depois rode ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey; para verificar as linhas antigas sem bloquear escritas. Para mudar a ação ON DELETE de uma chave existente, remova a restrição e adicione de novo num mesmo ALTER TABLE.

Indexar a coluna da chave estrangeira

O PostgreSQL indexa o lado referenciado, não a coluna que referencia. Sem um índice em orders.customer_id, toda exclusão em customers percorre a tabela orders inteira procurando filhas, e o mesmo acontece ao buscar os pedidos de um cliente:

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

Para listar as chaves estrangeiras de uma tabela, consulte pg_constraint:

Os joins por essas chaves estão na página de joins, e o capítulo de restrições do manual traz todas as regras, inclusive chaves adiáveis.

Perguntas frequentes

O PostgreSQL cria um índice para a chave estrangeira?

Não na coluna que referencia. A coluna referenciada já tem um, porque precisa ser chave primária ou única. Crie você mesmo o índice na coluna da chave estrangeira, já que os joins e as exclusões na tabela pai fazem buscas nela.

Como remover uma chave estrangeira no PostgreSQL?

ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;. Chaves estrangeiras sem nome se chamam table_column_fkey; \d orders no psql mostra o nome exato.

Uma coluna de chave estrangeira pode ser NULL no PostgreSQL?

Pode. Um NULL na coluna da chave estrangeira não é verificado, então a linha não precisa de pai. Adicione NOT NULL quando toda linha precisar ter um.

Uma chave estrangeira pode referenciar a mesma tabela?

Pode. Uma coluna como manager_id int REFERENCES employees (id) aponta para outra linha da mesma tabela. É assim que se guardam árvores como organogramas e threads de comentários.

Ilustração das linguagens de programação do Coddy

Aprenda a programar com o Coddy

Começar