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ção | Quando a linha pai é apagada |
|---|---|
NO ACTION (padrão) | A exclusão falha enquanto existirem linhas filhas |
RESTRICT | Igual, mas a verificação não pode ser adiada para o fim da transação |
CASCADE | As linhas filhas também são apagadas |
SET NULL | A coluna da chave estrangeira na filha vira NULL |
SET DEFAULT | A 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.