SQL-запросы PostgreSQL: поиск таблиц с определённым внешним ключом

Быстрый ответ

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

SELECT
  tbl.relname AS "Таблица",
  col.attname AS "Столбец"
FROM
  pg_constraint con
JOIN pg_class tbl ON con.conrelid = tbl.oid
JOIN pg_attribute col ON col.attrelid = tbl.oid AND col.attnum = ANY(con.conkey)
WHERE
  con.confrelid = (SELECT oid FROM pg_class WHERE relname = 'ЦелеваяТаблица') AND
  con.contype = 'f';

Пожалуйста, замените в нем 'ЦелеваяТаблица' на имя нужной вам таблицы.

Раскрытие секретов information_schema

Схема information_schema – это незаменимый инструментарий для анализа связей между внешними ключами. Основываясь на системных представлениях типа table_constraints, referential_constraints и key_column_usage, мы можем эффективно искать необходимую информацию:

-- Достаточно элегантно
SELECT
  kcu.table_schema,
  kcu.table_name,
  kcu.column_name,
  rc.update_rule,
  rc.delete_rule
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.referential_constraints AS rc ON tc.constraint_name = rc.constraint_name
WHERE
  kcu.referenced_column_name = 'ЦелеваяКолонка' AND
  tc.constraint_type = 'FOREIGN KEY';

Не забудьте заменить 'ЦелеваяКолонка' на имя нужного вам столбца.

Общий объект исследования

Когда у вас большая база данных с множеством схем, необходимо быть максимально точным в составлении запросов. Для повышения точности результатов следует усложнить запрос:

-- В таких случаях совсем необходимо увеличительное стекло
SELECT
  conname as "Ограничение",
  (SELECT relname FROM pg_class WHERE oid = con.conrelid) as "Таблица",
  attname as "Столбец"
FROM
  pg_constraint con
INNER JOIN pg_attribute att ON att.attrelid = con.conrelid AND att.attnum = ANY(con.conkey)
WHERE
  con.confrelid IN (SELECT oid FROM pg_class WHERE relname = 'ЦелеваяТаблица' AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = 'ЦелеваяСхема'))
  AND con.contype = 'f';

Для этого скрипта вместо 'ЦелеваяТаблица' и 'ЦелеваяСхема' подставьте текущие значения.

Ограничения: история их создания

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

Визуализация

Можно воспринимать этот процесс как поиск клада:

🗺️ (Карта сокровищ):

📍 обозначает таблицу с указанным внешним ключом.

SQL-запрос аккуратно указывает путь:

-- Мы можем найти сокровища с помощью SQL!
SELECT table_name
FROM information_schema.key_column_usage
WHERE referenced_column_name = 'ваше_имя_столбца';

Следуя указаниям нашего компаса, мы находим 'X' — здесь спрятаны сокровища:

🗺️: [📍Таблица1, 📍Таблица2, ...]

Поздравляем, ваш поиск сокровищ увенчался успехом! 🏴‍☠️

Взаимодействие инструментов для точности

Для изучения зависимостей на уровне разных схем или исключения некоторых схем применение различных SQL-инструментов может быть полезным. Комбинирование запросов позволит вам достичь желаемого результата:

-- Представьте, это универсальный запрос, такой Вольтрон в мире баз данных.
SELECT
  nsp.nspname as "Схема",
  tbl.relname as "Таблица",
  att.attname as "Столбец"
FROM
  pg_attribute att
JOIN
  pg_class tbl ON att.attrelid = tbl.oid
JOIN
  pg_namespace nsp ON tbl.relnamespace = nsp.oid
JOIN
  pg_constraint con ON att.attrelid = con.conrelid AND att.attnum = ANY(con.conkey)
WHERE
  con.contype = 'f'
  AND EXISTS (
    SELECT 1
    FROM pg_class ref_tbl
    JOIN pg_attribute ref_att ON ref_att.attrelid = ref_tbl.oid
    WHERE ref_tbl.relname = 'ЦелеваяТаблица' AND ref_att.attname = 'ЦелеваяКолонка' AND ref_tbl.relnamespace = nsp.oid
  );

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

Полезные материалы

1. PostgreSQL: Документация: 16: Глава 37. Схема информации – Официальная документация PostgreSQL по информационной схеме.
2. Как перечислить внешние ключи таблицы – Stack Overflow – Сообщество Stack Overflow поможет вам разобраться с внешними ключами.
3. dbdiagram.io – Инструмент для проектирования схем баз данных – Инструмент для визуализации схем баз данных, ради упрощения восприятия сложных структур.
4. Нахождение зависимостей – Вики PostgreSQL – Инструкция будет вашим надежным помощником в поиске зависимостей.
5. CTE в PostgreSQL – Изучите новые возможности CTE.
6. SchemaSpy • Лёгкое документирование баз данных. – Развивайте свои навыки анализа и документирования схем баз данных, станьте мастером PostgreSQL.