Создание внешних ключей к неосновным полям в SQL

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

Внешний ключ может ссылаться на уникальный ключ другой таблицы, не обязательно на первичный ключ. Для обеспечения целостности данных необходимо удостовериться, что указанный столбец обладает ограничением уникальности (UNIQUE constraint). Приведем пример:

-- Создание таблицы "Hulk", без задания первичного ключа!
CREATE TABLE Hulk (
    SmashFactor INT UNIQUE,
    ...
);
-- Таблица "Avengers" с внешним ключом, который ссылается на фактор величины урона Халка
CREATE TABLE Avengers (
    HulkSmash INT,
    ...
    FOREIGN KEY (HulkSmash) REFERENCES Hulk(SmashFactor)
);

В этом примере столбец HulkSmash из таблицы Avengers ссылается на SmashFactor в таблице Hulk, который, хоть и не является первичным ключом, гарантирует уникальность.

Когда рекомендуется использовать связи на основе не первичных ключей

Устаревшие системы

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

Оптимизация запросов

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

Мягкое удаление

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

Типичные проблемы и лучшие практики

Целостность данных

Сохранение уникальности – ключ к порядку и стабильности в данных. Нельзя допускать ослабление уникальности.

Совместимость приложений

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

Проверочные ограничения

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

Внешние ключи, ссылающиеся на не первичные ключи – шаг вперед

Сложные отношения

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

Уникальные идентификаторы

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

Функциональная целостность

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

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

Теперь рассмотрим внешний ключ, который не ссылается на первичный ключ:

Представим библиотеку 📚, где каждая книга (📘) имеет свой уникальный "Код Жанра", помимо ISBN.

Этот "Код Жанра" аналогичен уникальному идентификатору на полке (🏷️), который связан с "Списком желаемых книг" (📝) через внешний ключ:

Список "Книги к прочтению" (📝): | Название книги | Код Жанра (🏷️) | | -------------- | -------------- | | Основы SQL | НФ |

Книжные полки (📚): | Код Жанра (🏷️) | Жанр книги | | -------------- | ---------------- | | НФ | Научная фантастика |

Ссылка без использования ISBN (🔗): 📝 -> 🏷️ = 📘 (Вот это научная фантастика... без ракет!)

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

Оптимизация связей с не первичными ключами

Именование ограничений

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

Настройка производительности

Добавляйте индексы к столбцам, чтобы оптимизировать выполнение запросов и ускорить работу системы.

Анализ данных

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

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

1. MySQL :: Руководство по MySQL 8.0 :: 13.1.20.5 FOREIGN KEY Constraints — Официальное руководство по определению внешних ключей в MySQL.
2. Создание отношений внешних ключей – SQL Server | Microsoft Learn — Руководство Microsoft по настройке отношений внешних ключей в SQL Server.
3. PostgreSQL: Документация: 16: 5.4 Constraints — Обучающая статья об особенностях работы с внешними ключами и задании ограничений в PostgreSQL.
4. Нормализация баз данных | Управление базами данных | Fandom — Обзор нормализации баз данных, включая случаи использования внешних ключей, ссылающихся на ключи, не являющиеся первичными.
5. ER-диаграмма (ERD) – Определение и обзор | Lucidchart — Рассматривает ER-диаграммы, в том числе и используемые в них внешние ключи, ссылающиеся на не первичные ключи.