Поиск связанных таблиц в Oracle SQL Developer: способы и утилиты

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

Для поиска таблиц, ссылающихся на определённую таблицу в Oracle SQL, можно воспользоваться следующим SQL-запросом:

-- SQL-скрипт: "Детектив: База данных"
SELECT a.table_name, a.constraint_name
FROM user_cons_columns a
JOIN user_constraints c ON a.constraint_name = c.constraint_name
WHERE c.r_constraint_name IN (
   SELECT constraint_name FROM user_constraints
   WHERE table_name = 'ВАША_ТАБЛИЦА' AND constraint_type = 'P'
)
AND c.constraint_type = 'R';

Поменяйте 'ВАША_ТАБЛИЦА' на название нужной вам таблицы, чтобы запрос корректно выполнился.

Детали и возможные трудности

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

Чувствительность к регистру: слабое место Oracle

Oracle по умолчанию преобразует идентификаторы к верхнему регистру. Недостаточное внимание к регистру может стать источником проблем.

-- Регистрозависимый SQL
WHERE table_name = 'Target_Table' -- Допустимо, если 'Target_Table' была создана без кавычек.
WHERE table_name = 'TARGET_TABLE' -- Правильный вариант для идентификаторов в верхнем регистру.

Подробнее об этом расскажет наш спикер на видео

Расширение области поиска: представление ALL_CONSTRAINTS

Для просмотра ссылок из всех пользовательских схем можно использовать ALL_CONSTRAINTS.

-- Поиск по всем таблицам
SELECT a.table_name AS Referencing_Table,
       concat('References ', c.r_table_name) AS Reference_Type
FROM all_cons_columns a
JOIN all_constraints c ON a.constraint_name = c.constraint_name
WHERE c.constraint_type = 'R'
  AND c.r_table_name = 'ВАША_ТАБЛИЦА'; -- Укажите нужное вам имя таблицы.

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

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

Железнодорожная станция (🚉): Сотрудники

Поезд 1 (🚂): Заказы
Поезд 2 (🚃): Заработки
Поезд 3 (🚋): Отчёты

Каждый поезд движется по своему пути к станции.

🚂 Заказы --🛤️--> (🚉) Сотрудники
🚃 Заработки --🛤️--> (🚉) Сотрудники
🚋 Отчёты --🛤️--> (🚉) Сотрудники

Глубокое понимание Oracle SQL Developer

Визуальное представление во вкладке "Модель"

В SQL Developer 4.1 и более поздних версиях во вкладке "Модель" можно наглядно увидеть связи между таблицами, что отражает структуру отношений внешних ключей.

Расширенные возможности благодаря плагинам

SQL Developer Data Modeler — это расширение, которое предлагает продвинутые инструменты для работы с БД, включая подробные ER-диаграммы.

Инструменты > Настройки > Расширения > Добавить > Применить изменения

Малоизвестная особенность отчёта "All tables / Dependencies"

Отчет "All tables / Dependencies" может запутать вас, так как он показывает зависимости внутри таблицы, а не между таблицами.

Усовершенствование ваших SQL-запросов

Связывание первичных и внешних ключей

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

SELECT uc.table_name, uc.constraint_name
FROM all_constraints uc
WHERE uc.constraint_type IN ('U', 'P')
  AND EXISTS (
    SELECT 1 FROM all_constraints fc
    WHERE fc.r_constraint_name = uc.constraint_name
  );

Взаимосвязи между пользователями

При работе с несколькими пользователями важно изучить отношения между схемами.

SELECT ac.owner AS referencing_owner,
       ac.table_name AS referencing_table,
       acc.column_name AS referencing_column,
       acc.position AS key_position,
       ac2.owner AS referenced_owner,
       ac2.table_name AS referenced_table
FROM all_cons_columns acc
JOIN all_constraints ac ON acc.owner = ac.owner
                        AND acc.constraint_name = ac.constraint_name
JOIN all_constraints ac2 ON ac.r_owner = ac2.owner
                         AND ac.r_constraint_name = ac2.constraint_name
WHERE ac2.table_name = 'ВАША_ТАБЛИЦА'
  AND ac.constraint_type = 'R';

Упрощение работы с вкладкой FK References

Вкладка FK References предоставляет информацию о внешних ключах, что ускоряет и упрощает работу.

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

1. "Ask TOM" – о поиске отношений с определенной таблицей в Oracle.
2. "Oracle DBA Scripts All in One" – полезные скрипты для администратора Oracle.
3. "Tech on the Net – Oracle / PLSQL" – руководство по работе с внешними ключами в Oracle PL/SQL.
4. Обсуждение на StackOverflow о методах поиска таблиц, ссылкающихся на другие, в Oracle.