PostgreSQL : SQL script pour obtenir une liste de toutes les tables qui ont une colonne particulière comme clé étrangère.

Question

J'utilise PostgreSQL et j'essaie de lister toutes les tables qui ont une colonne particulière d'une table comme clé étrangère/référence. Est-ce possible ? Je suis sûr que cette information est stockée quelque part dans le fichier information_schema mais je n'ai aucune idée de la façon dont je dois m'y prendre.

Answer 1

select R.TABLE_NAME
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE u
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS FK
    on U.CONSTRAINT_CATALOG = FK.UNIQUE_CONSTRAINT_CATALOG
    and U.CONSTRAINT_SCHEMA = FK.UNIQUE_CONSTRAINT_SCHEMA
    and U.CONSTRAINT_NAME = FK.UNIQUE_CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE R
    ON R.CONSTRAINT_CATALOG = FK.CONSTRAINT_CATALOG
    AND R.CONSTRAINT_SCHEMA = FK.CONSTRAINT_SCHEMA
    AND R.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
WHERE U.COLUMN_NAME = 'a'
  AND U.TABLE_CATALOG = 'b'
  AND U.TABLE_SCHEMA = 'c'
  AND U.TABLE_NAME = 'd'

Ceci utilise le triplet complet catalogue/schéma/nom pour identifier une table db à partir des 3 vues information_schema. Vous pouvez en supprimer une ou deux selon vos besoins.

La requête liste toutes les tables qui ont une contrainte de clé étrangère sur la colonne 'a' dans la table 'd'.

Answer 2

Les autres solutions ne sont pas garanties pour fonctionner avec postgresql. car l'unicité du nom de la contrainte n'est pas garantie ; vous obtiendrez donc des faux positifs. PostgreSQL avait l'habitude de nommer les contraintes avec des noms stupides comme '$1', et si vous avez une vieille base de données que vous avez maintenue à travers les mises à jour, vous avez probablement encore quelques-unes de ces contraintes.

Puisque cette question s'adressait à PostgreSQL et que c'est ce que vous utilisez, vous pouvez interroger les tables internes de Postgres, pg_class et pg_attribute, pour obtenir un résultat plus précis.

NOTE : Les FKs peuvent être sur plusieurs colonnes, donc la colonne de référence (attnum de pg_attribute) est un ARRAY, ce qui est la raison de l'utilisation de array_agg dans la réponse.

La seule chose que vous devez brancher est le TARGET_TABLE_NAME :

select 
  (select r.relname from pg_class r where r.oid = c.conrelid) as table, 
  (select array_agg(attname) from pg_attribute 
   where attrelid = c.conrelid and ARRAY[attnum] <@ c.conkey) as col, 
  (select r.relname from pg_class r where r.oid = c.confrelid) as ftable 
from pg_constraint c 
where c.confrelid = (select oid from pg_class where relname = 'TARGET_TABLE_NAME');

Si vous voulez aller dans l'autre sens (énumérer toutes les choses auxquelles une table spécifique fait référence), il suffit de modifier la dernière ligne comme suit :

where c.conrelid = (select oid from pg_class where relname = 'TARGET_TABLE_NAME');

Oh, et puisque la question actuelle était de cibler une colonne spécifique, vous pouvez spécifier le nom de la colonne avec celui-ci :

select (select r.relname from pg_class r where r.oid = c.conrelid) as table, 
       (select array_agg(attname) from pg_attribute 
        where attrelid = c.conrelid and ARRAY[attnum] <@ c.conkey) as col, 
       (select r.relname from pg_class r where r.oid = c.confrelid) as ftable 
from pg_constraint c 
where c.confrelid = (select oid from pg_class where relname = 'TARGET_TABLE_NAME') and 
      c.confkey @> (select array_agg(attnum) from pg_attribute 
                    where attname = 'TARGET_COLUMN_NAME' and attrelid = c.confrelid);

Answer 3

Cette requête nécessite sólo la référence nom du tableau y nom de la colonne et produit un ensemble de résultats contenant les deux côtés de la clé étrangère.

select confrelid::regclass, af.attname as fcol,
       conrelid::regclass, a.attname as col
from pg_attribute af, pg_attribute a,
  (select conrelid,confrelid,conkey[i] as conkey, confkey[i] as confkey
   from (select conrelid,confrelid,conkey,confkey,
                generate_series(1,array_upper(conkey,1)) as i
         from pg_constraint where contype = 'f') ss) ss2
where af.attnum = confkey and af.attrelid = confrelid and
      a.attnum = conkey and a.attrelid = conrelid 
  AND confrelid::regclass = 'my_table'::regclass AND af.attname = 'my_referenced_column';

---

Exemple d'ensemble de résultats :

confrelid |         fcol         |   conrelid    |     col
----------+----------------------+---------------+-------------
 my_table | my_referenced_column | some_relation | source_type
 my_table | my_referenced_column | some_feature  | source_type

---

Tout le mérite en revient à Lane et Krogh sur le forum PostgreSQL .

Answer 4

Personnellement, je préfère effectuer une requête basée sur la contrainte unique référencée plutôt que sur la colonne. Cela ressemblerait à quelque chose comme ceci :

SELECT rc.constraint_catalog,
       rc.constraint_schema||'.'||tc.table_name AS table_name,
       kcu.column_name,
       match_option,
       update_rule,
       delete_rule
FROM information_schema.referential_constraints AS rc 
    JOIN information_schema.table_constraints AS tc USING(constraint_catalog,constraint_schema,constraint_name)
    JOIN information_schema.key_column_usage AS kcu USING(constraint_catalog,constraint_schema,constraint_name)
WHERE unique_constraint_catalog='catalog'
    AND unique_constraint_schema='schema'
    AND unique_constraint_name='constraint name';

---

Voici une version qui permet d'effectuer des requêtes par nom de colonne :

SELECT rc.constraint_catalog,
       rc.constraint_schema||'.'||tc.table_name AS table_name,
       kcu.column_name,
       match_option,
       update_rule,
       delete_rule
FROM information_schema.referential_constraints AS rc
    JOIN information_schema.table_constraints AS tc USING(constraint_catalog,constraint_schema,constraint_name)
    JOIN information_schema.key_column_usage AS kcu USING(constraint_catalog,constraint_schema,constraint_name)
    JOIN information_schema.key_column_usage AS ccu ON(ccu.constraint_catalog=rc.unique_constraint_catalog AND ccu.constraint_schema=rc.unique_constraint_schema AND ccu.constraint_name=rc.unique_constraint_name)
WHERE ccu.table_catalog='catalog'
    AND ccu.table_schema='schema'
    AND ccu.table_name='name'
    AND ccu.column_name='column';

Answer 5

Une simple demande pour récupérer les noms des clés étrangères ainsi que les noms des tables :

SELECT CONSTRAINT_NAME, table_name
FROM
   information_schema.table_constraints 
WHERE table_schema='public' and constraint_type='FOREIGN KEY'