Comment lister les clés étrangères d'une table

Question

Existe-t-il un moyen d'utiliser SQL pour lister toutes les clés étrangères pour une table donnée ? Je connais le nom de la table / le schéma et je peux l'introduire.

Answer 1

Vous pouvez le faire via les tables information_schema. Par exemple :

SELECT
    tc.table_schema, 
    tc.constraint_name, 
    tc.table_name, 
    kcu.column_name, 
    ccu.table_schema AS foreign_table_schema,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
      AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
      AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name='mytable';

Answer 2

Psql fait cela, et si vous démarrez psql avec :

psql -E

il vous montrera exactement quelle requête est exécutée. Dans le cas de la recherche de clés étrangères, c'est :

SELECT conname,
  pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '16485' AND r.contype = 'f' ORDER BY 1

Dans ce cas, 16485 est l'oid de la table que je regarde - vous pouvez l'obtenir en convertissant votre nom de table en regclass comme suit :

WHERE r.conrelid = 'mytable'::regclass

Qualifiez le nom de la table en fonction du schéma s'il n'est pas unique (ou s'il n'est pas le premier de la liste de votre search_path ) :

WHERE r.conrelid = 'myschema.mytable'::regclass

Answer 3

La réponse d'Ollyc est bonne car elle n'est pas spécifique à Postgres, cependant, elle s'effondre lorsque la clé étrangère fait référence à plus d'une colonne. La requête suivante fonctionne pour un nombre arbitraire de colonnes mais elle s'appuie fortement sur les extensions de Postgres :

select 
    att2.attname as "child_column", 
    cl.relname as "parent_table", 
    att.attname as "parent_column",
    conname
from
   (select 
        unnest(con1.conkey) as "parent", 
        unnest(con1.confkey) as "child", 
        con1.confrelid, 
        con1.conrelid,
        con1.conname
    from 
        pg_class cl
        join pg_namespace ns on cl.relnamespace = ns.oid
        join pg_constraint con1 on con1.conrelid = cl.oid
    where
        cl.relname = 'child_table'
        and ns.nspname = 'child_schema'
        and con1.contype = 'f'
   ) con
   join pg_attribute att on
       att.attrelid = con.confrelid and att.attnum = con.child
   join pg_class cl on
       cl.oid = con.confrelid
   join pg_attribute att2 on
       att2.attrelid = con.conrelid and att2.attnum = con.parent

Answer 4

Extension à la recette ollyc :

CREATE VIEW foreign_keys_view AS
SELECT
    tc.table_name, kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM
    information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage 
        AS kcu ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage 
        AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY';

Ensuite :

SELECT * FROM foreign_keys_view WHERE table_name='YourTableNameHere' ;

Answer 5

Vous pouvez utiliser le Catalogues système PostgreSQL . Vous pouvez peut-être demander pg_constraint pour demander des clés étrangères. Vous pouvez également utiliser la fonction Schéma d'information