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


Anúncios

Publicado em 23 de março de 2009, em PostgreSQL e marcado como , , , , . Adicione o link aos favoritos. 3 Comentários.

  1. Muito obrigado. Foi de grande ajuda, tava tentando montar um select igual ao feito em mysql mas alguns parametros faltam, entre eles se o campo é chave ou não, então com esta dica já posso adaptar para postgress. (O select mysql abaixo)

    select TABLE_NAME, COLUMN_NAME, DATA_TYPE ,COLUMN_TYPE ,COLUMN_KEY, IS_NULLABLE from information_schema.columns where TABLE_SCHEMA =’REPLICACAO’

  2. Exite uma função como esta pg_get_constraintdef() [PostgreSQL] para SQL?
    Preciso de uma dessas URGENTE, pois não consigo achar.

    Muito Obrigado!

  3. Olá, Jefferson

    Na verdade esta é uma função do PostgreSQL, acredito que não exista em SQL puro não, mas não tenho certeza, precisaria conferir a documentação.

    Obrigado.

    Abraços

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: