SQL-запрос для получения всех внешних ключей таблицы

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

Чтобы получить список всех внешних ключей базы данных, можно воспользоваться схемой information_schema. Вот пример такого запроса для SQL Server:

SELECT 
  fk.name AS FK_name,
  tp.name AS parent_table,
  cp.name AS parent_column,
  tr.name AS referenced_table,
  cr.name AS referenced_column
FROM 
  sys.foreign_keys AS fk
  INNER JOIN sys.foreign_key_columns AS fkc ON fk.object_id = fkc.constraint_object_id
  INNER JOIN sys.tables AS tp ON fk.parent_object_id = tp.object_id
  INNER JOIN sys.columns AS cp ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
  INNER JOIN sys.tables AS tr ON fk.referenced_object_id = tr.object_id
  INNER JOIN sys.columns AS cr ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id;

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

Подробнее о теме

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

Подход в PostgreSQL

В PostgreSQL для данной цели можно воспользоваться каталогом pg_catalog:

SELECT 
  conname AS constraint_name,
  (SELECT relname FROM pg_class WHERE oid = conrelid) AS table_name,
  attname AS column_name
FROM 
  pg_constraint, 
  LATERAL unnest(conkey) AS myColumn(attNum),
  LATERAL (SELECT attname FROM pg_attribute WHERE attrelid = conrelid AND attnum = myColumn.attNum) AS myColName
WHERE 
  contype = 'f'
  AND conrelid = 'your_table_name'::regclass;

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

Метод в MySQL

Для MySQL можно использовать следующий запрос:

SELECT 
  TABLE_NAME,
  COLUMN_NAME,
  CONSTRAINT_NAME,
  REFERENCED_TABLE_NAME,
  REFERENCED_COLUMN_NAME
FROM 
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE 
  REFERENCED_TABLE_SCHEMA = 'your_database_name'
  AND TABLE_NAME = 'your_table_name';

Синтаксис в SQLite

SQLite предпочитает команду PRAGMA для работы с внешними ключами:

PRAGMA foreign_key_list('your_table_name');

Работа с составными внешними ключами

В PostgreSQL запрос к составным внешним ключам может быть выполнен следующим образом:

SELECT 
  conname,
  conrelid::regclass,
  unnest(conkey) AS conkey_unnest
FROM 
  pg_constraint
WHERE 
  contype = 'f';

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

Можно представить таблицы как здания 🏛️, а внешние ключи — как дороги🛣️ соединяющие эти здания:

🏛️ Таблица A имеет внешний ключ на 🏛️ Таблицу B
🛣️: A 🔗 B
# Направление связи указывает на одностороннее перемещение. 🚦

Каждый внешний ключ формирует путь от одного здания к другому:

🏛️ Таблица C ➡️ 🏛️ Таблица D 🛣️ (Таблица C ссылается на Таблицу D).

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

Продвинутые возможности

Ускоренный доступ к внешним ключам

В PostgreSQL можно ускорить доступ к внешним ключам с помощью создания представления:

CREATE OR REPLACE VIEW foreign_keys_view AS
...

Разрешение конфликтов имен

В PostgreSQL при возникновении проблем с повторяющимися именами можно использовать квалифицированные имена для их разрешения.

Использование дополнений к Postgres

Если вам нужны дополнительные возможности для расширения запросов в PostgreSQL, рекомендуется ознакомиться с расширениям adminpack.

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

1. Документация PostgreSQL по схеме информации — подробное руководство по работе с внешними ключами в PostgreSQL.
2. Таблица KEY_COLUMN_USAGE в информационной схеме MariaDB — описание методов извлечения информации о внешних ключах из информационной схемы MariaDB.
3. Документация SQLite по PRAGMA — детальное руководство по работе с ограничениями внешнего ключа в SQLite.
4. Ограничение FOREIGN KEY в SQL на сайте W3Schools — обучающее руководство по использованию ограничения FOREIGN KEY.
5. Визуализация SQL JOIN с понятным объяснением – блог Джеффа Этвуда — помогает визуализировать SQL JOIN и лучше их понимать.
6. Диаграмма «сущность-связь» (ERD) – определение и обзор — руководство для понимания и создания ER-диаграмм.