SQL, um alle Tabellen aufzulisten, die auf eine bestimmte Spalte in einer Tabelle verweisen

Bin ich mit PostgreSQL und ich versuche, um eine Liste aller Tabellen, in denen eine bestimmte Spalte aus einer Tabelle als foreign-key-Referenz. Kann das getan werden? Ich bin sicher, dass die Daten irgendwo gespeichert sind, in information_schema aber ich habe keine Ahnung, wie man Abfragen.

InformationsquelleAutor der Frage Gaurav Dadhania | 2011-03-18

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'

Dieser nutzt die volle catalog/schema/name Drilling zu identifizieren, die eine db-Tabelle für alle 3 information_schema-sichten. Sie können lassen Sie ein oder zwei wie nötig.

Die query zeigt alle Tabellen, die eine foreign key-Einschränkung gegen die Spalte 'a' in Tabelle 'd'

InformationsquelleAutor der Antwort RichardTheKiwi

Die anderen Lösungen sind nicht garantiert, um zu arbeiten in postgresql, als die constraint_name nicht garantiert einzigartig zu sein; somit erhalten Sie false-positives. PostgreSQL verwendet, um name-Einschränkungen dumme Dinge wie '$1', und wenn Sie habe eine alte Datenbank, die Sie habe die Pflege durch upgrades, die Sie wahrscheinlich noch einige von denen Rum.

Da diese Frage richtete sich AN PostgreSQL-und das ist, was Sie verwenden, dann können Sie die Abfrage der internen postgres-Tabellen pg_class und pg_attribute um ein genaueres Ergebnis.

HINWEIS: FKs kann auf mehrere Spalten, damit das referenzierende Spalte (attnum von pg_attribute) ist ein ARRAY, was ist der Grund für die Verwendung array_agg in der Antwort.

Das einzige, was Sie brauchen plug-in ist die 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');

Wenn Sie möchten, zu gehen die andere Weise (Liste all der Dinge, die eine bestimmte Tabelle bezieht), dann ändern Sie einfach die Letzte Zeile zu:

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

Oh, und da die eigentliche Frage war gezielt für einen bestimmten Spalte können Sie den Namen der Spalte mit dieser Formel:

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);

InformationsquelleAutor der Antwort Tony K.

Persönlich, ich bevorzuge die Abfrage basierend auf der unique-Einschränkung verwiesen wird, vielmehr als die Spalte. Das würde in etwa so Aussehen:

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';

Hier ist eine version, die die Abfrage nach der Spalte name:

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';

InformationsquelleAutor der Antwort Anomie

Diese Abfrage erfordert nur auf die verwiesen wird Tabelle name und Spalte name, und erzeugt eine Ergebnismenge, die sowohl die Seiten der Fremdschlüssel.

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';

Beispiel ResultSet:

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

Alle Kredit Lane und Krogh an der PostgreSQL-forum.

InformationsquelleAutor der Antwort vallismortis

SELECT
  main_table.table_name            AS main_table_table_name,
  main_table.column_name           AS main_table_column_name,
  main_table.constraint_name       AS main_table_constraint_name,
  info_other_table.table_name      AS info_other_table_table_name,
  info_other_table.constraint_name AS info_other_table_constraint_name,
  info_other_table.column_name     AS info_other_table_column_name
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE main_table
  INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS other_table
    ON other_table.unique_constraint_name = main_table.constraint_name
  INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE info_other_table
    ON info_other_table.constraint_name = other_table.constraint_name
WHERE main_table.table_name = 'MAIN_TABLE_NAME';

InformationsquelleAutor der Antwort Lucas

Einer einfachen Anfrage für erholt die Namen der Fremdschlüssel sowie die Namen der Tabellen:

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

InformationsquelleAutor der Antwort Fadid

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.