Разница между внешним и ссылочным ключами в SQL

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

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

ALTER TABLE Orders
ADD FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

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

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

Терминология: Внешние и Ссылочные ключи

Термин "ссылочный ключ" не столь распространен в SQL. Но его употребление может означать как внешний ключ, так и первичный ключ, в зависимости от контекста.

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

Выбор стратегии ограничений: на уровне столбца или на уровне таблицы

Выбор ограничений, которые нужно использовать, базируется не только на спецификах СУБД, но и на требованиях к читаемости кода. Например, MySQL поддерживает внешние ключи на уровне таблицы, в то время как PostgreSQL и SQL Server позволяют использовать их как на уровне отдельных столбцов, так и на уровне таблицы в целом.

Ограничение на уровне столбца добавляется непосредственно при объявлении столбца:

-- Я пример ограничения на уровне столбца!
-- Маленький и проворный, я занят конкретным полем.
CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    CustomerID int REFERENCES Customers(CustomerID)
);

А вот ограничение на уровне таблицы устанавливается после определения всех столбцов:

-- Я пример ограничения на уровне таблицы!
-- Большой и представительный, я отвечаю за все поля сразу.
CREATE TABLE Orders (
    OrderID int,
    CustomerID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

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

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

Можно вообразить вечеринку (💃🕺), где каждый из участников — это отдельная таблица в базе данных:

Таблица A (💃): [Гость 1, Гость 2, Гость 3] — ищут партнера для танца
Таблица B (🕺): [Гость 2, Гость 3, Гость 4] — готовы продемонстрировать свои танцевальные навыки

Внешний ключ в данной ситуации — это DJ на вашей уютной вечеринке:

💃🔊🕺: [Гость 2, Гость 3]
# Внешний ключ — это как DJ, подшивающий наличие подходящих партнеров из танцевальной таблицы B для гостей таблицы A!

Ссылочный ключ, в свою очередь, — это процесс подбора танцевальных пар:

💃💍🕺: [Гость 2, Гость 3]
# Ссылочный ключ обозначает согласование между танцевающими гостями из таблицы B и желающими приобщиться к танцу гостями из таблицы A.

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

Ориентиры в мире СУБД

Работа с ограничениями в MySQL

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

Гибкость в PostgreSQL и SQL Server

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

Упрощение синтаксиса через REFERENCES без FOREIGN KEY

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

Работа со сложными многоколоночными ключами

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

-- Большие возможности посему требуют большой ответственности 🕷️
-- Ограничение на уровне таблицы – мастерски управляет сложными отношениями!
CREATE TABLE OrderDetails (
    OrderID int,
    ProductID int,
    FOREIGN KEY (OrderID, ProductID) REFERENCES Orders(OrderID, ProductID)
);

Таким образом, мы не просто связываем OrderDetails и Orders, мы создаем надежный механизм для поддержания целостности данных.

Лучшие практики и сохранение ссылочной целостности

Важно тщательно изучить документацию по выбранной вами СУБД, словно вы готовитесь к важному экзамену. Глубокое понимание возможностей СУБД позволит наилучшим образом использовать внешние ключи для обеспечения целостности данных и общей надежности системы.

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

1. SQL FOREIGN KEY Constraint — обширная информация об особенностях внешних ключей в SQL.
2. Создание внешних ключевых связей – SQL Server | Microsoft Learn — руководство от Microsoft по созданию внешних ключевых связей в SQL Server.
3. constraint — Исчерпывающее руководство по ограничениям внешних ключей от Oracle для поддержания непревзойденной целостности баз данных.
4. SQL Server 2005: Первичные и Внешние ключи — Полезный материал для понимания различных ключевых связей в SQL Server, ценный ресурс для освоения как первичных, так и внешних ключей.