SQL для отображения всех таблиц, которые ссылаются на конкретный столбец в таблице

Ответ 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'

Здесь используется полный каталог/схема/имя триплета, чтобы идентифицировать таблицу db из всех трех видов информации. При необходимости вы можете удалить один или два.

В запросе перечислены все таблицы, которые имеют ограничение внешнего ключа в столбце "a" в таблице "d"

Ответ 2

Другие решения не гарантируются для работы в postgresql, поскольку имя ограничения не гарантировано является уникальным; таким образом, вы получите ложные срабатывания. PostgreSQL используется для обозначения ограничений таких глупых вещей, как "$ 1", и если у вас есть старая база данных, которую вы поддерживаете с помощью обновлений, у вас, вероятно, все еще есть некоторые из них.

Поскольку этот вопрос был нацелен на PostgreSQL, и именно это вы используете, вы можете запросить внутренние таблицы postgres pg_class и pg_attribute, чтобы получить более точный результат.

ПРИМЕЧАНИЕ. FK могут быть в нескольких столбцах, поэтому столбец ссылки (attnum pg_attribute) является ARRAY, что является причиной использования array_agg в ответе.

Единственное, что вам нужно подключить, это 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');

Если вы хотите пойти другим путем (перечислите все вещи, на которые ссылается конкретная таблица), просто измените последнюю строку на:

where c.conrelid = (select oid from pg_class where relname = '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') and 
      c.confkey @> (select array_agg(attnum) from pg_attribute 
                    where attname = 'TARGET_COLUMN_NAME' and attrelid = c.confrelid);

Ответ 3

Лично я предпочитаю запрашивать на основе ссылочного уникального ограничения, а не столбца. Это будет выглядеть примерно так:

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

Вот версия, которая позволяет запрашивать имя столбца:

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

Ответ 4

Этот запрос требует только имя таблицы и имя столбца, на которые ссылаются, и создает результирующий набор, содержащий обе стороны внешнего ключа.

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

Пример результирующего набора:

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

Вся заслуга Лейну и Крогу на форуме PostgreSQL.

Ответ 5

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

Ответ 6

Простой запрос на восстановление имен внешнего ключа, а также имен таблиц:

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

Ответ 7

Если вы используете клиент psql, вы можете просто выполнить команду \d table_name чтобы увидеть, какие таблицы ссылаются на данную таблицу. Со связанной страницы документации:

\d[S+] [ pattern ]

Для каждого отношения (таблицы, представления, материализованного представления, индекса, последовательности или внешней таблицы) или составного типа, соответствующего pattern, отобразите все столбцы, их типы, табличное пространство (если не по умолчанию) и любые специальные атрибуты, такие как NOT NULL или по умолчанию. Связанные индексы, ограничения, правила и триггеры также показаны. Для внешних таблиц также отображается связанный внешний сервер.