Descobrindo a definição de chave primária (primary key) e chave estrangeira (foreign key)

Olá, pessoal

Na última semana surgiu uma pergunta na lista pgbr-geral sobre como descobrir a definição das restrições (constraints) de chave primária (primary key) e chave estrangeira (foreign key). Então aproveito para escrever um pouquinho sobre o assunto.

Essa informação não é encontrada diretamente nas tabelas e visões do catálogo do PostgreSQL, mas sim através de uma função chamada pg_get_constraintdef(oid), onde o parâmetro oid é o oid da constraint e que pode ser encontrado na tabela pg_constraint. Por exemplo, existe uma visão chamada pg_indexes que mostra as definições dos índices, porém se pararmos para analisar esta consulta veremos que para apresentar a definição dos índices é utilizada uma função chamada pg_get_indexdef(oid) semelhante a função usada para extrair a definição das restrições (constraints).

Abaixo segue a consulta para identificar o nome da tabela e suas definições de chave primária (primary key) e chave estrangeira (foreing key). Para encontrar essa informação é necessário o uso das tabelas de sistema pg_class e pg_constraint. Coloquei também a tabela pg_namespace que é onde são armazenados os esquemas existentes do PostgreSQL.

SELECT pg_class.relname AS nome_da_tabela,

pg_get_constraintdef(pg_constraint.oid) AS definicao_da_restricao

FROM pg_namespace JOIN pg_class ON pg_namespace.oid=pg_class.relnamespace

JOIN pg_constraint ON pg_class.oid=pg_constraint.conrelid

WHERE pg_namespace.nspname=’public’

AND pg_class.relkind=’r’

ORDER BY pg_class.relname;

O resultado desta consulta apresenta o nome da tabela e a sua respectiva definição de chave primária (primary key) e chave estrangeira (foreign key). Caso a tabela não possua chave primária e/ou chave estrangeira a mesma não será retornada por esta consulta. Neste caso seria necessário substituir o INNER JOIN por um LEFT OUTER JOIN. A palavra OUTER no LEFT OUTER JOIN é opcional.

E a sua implementação ficaria assim:

SELECT pg_class.relname AS nome_da_tabela,

pg_get_constraintdef(pg_constraint.oid) AS definicao_da_restricao

FROM pg_namespace JOIN pg_class ON pg_namespace.oid=pg_class.relnamespace

LEFT OUTER JOIN pg_constraint ON pg_class.oid=pg_constraint.conrelid

WHERE pg_namespace.nspname=’public’

AND pg_class.relkind=’r’

ORDER BY pg_class.relname;

Na cláusula WHERE existem duas restrições: pg_namespace.nspname=’public’ restringe em qual esquema serão procurados os objetos, neste caso apenas no esquema public. Se for necessário procurar em mais de um esquema pode-se substituir a condição pg_namespace.nspname=’public’ por pg_namespace.nspname IN (‘public’, ‘outro_esquema’). A condição pg_class.relkind=’r’ restringe que só serão pesquisados os objetos do tipo tabela. Esta restrição é importante pois a tabela pg_class armazena outros objetos como: índices, seqüências, visões, tipos compostos e tabelas toast. Esta condição é necessária devido ao join com a tabela pg_namespace. Se essa junção for retirada não é necessária esta restrição.

Se for de interesse é possível mostrar o nome da restrição (constraint) através do atributo conname que está armazenado na tabela pg_constraint.

Era isso. Fiquem a vontade para comentários.

Abraços