Добавление именованных ограничений в SQL: SQL Server и PostgreSQL

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

Для установления связи между двумя таблицами в операторе CREATE TABLE применяются именованные внешние ключи. При создании ограничения после ключевого слова CONSTRAINT указывается FOREIGN KEY, затем название столбца, выступающего в роли внешнего ключа, и применяется ключевое слово REFERENCES для определения связываемой таблицы и столбца. Вот пример такого SQL-запроса:

CREATE TABLE Заказы (
    ИД_Заказа INT,
    ИД_Продукта INT,
    CONSTRAINT FK_Продукт FOREIGN KEY (ИД_Продукта)
    REFERENCES Продукты(ИД_Продукта) 
);

В данном примере FK_Продукт обеспечивает соответствие между ИД_Продукта в таблице Заказы и записью в таблице Продукты.

Зачем и как именовать ограничения внешнего ключа?

Преимущества именования

Именованные ограничения могут обеспечить ряд преимуществ:

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

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

Разработка системы именования

Для лучшего понимания устанавливаемых связей рекомендуется разработать систему именования вида FK_<ДочерняяТаблица>_<РодительскаяТаблица>_<Поле>.

Стандартный шаблон оформления в SQL

Ограничения можно включить в описание столбцов или добавить их в конце:

CREATE TABLE Сотрудник (
    ИД_Сотрудника INT,
    ИД_Отдела INT,
    CONSTRAINT FK_Сотрудник_Отдел_ИД_Сотрудника FOREIGN KEY (ИД_Отдела)
    REFERENCES Отдел(ИД_Отдела)
);

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

CREATE TABLE Сотрудник (
    ИД_Сотрудника INT,
    ИД_Отдела INT,
    CONSTRAINT FK_Сотрудник_Отдел FOREIGN KEY (ИД_Отдела)
    REFERENCES Отдел(ИД_Отдела)
);

Управление несколькими внешними ключами

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

CREATE TABLE ЗадачиСотрудников (
    ИД_Задачи INT,
    Назначен INT,
    Проверен INT,
    CONSTRAINT FK_ЗадачиСотрудников_Сотрудник_Назначен FOREIGN KEY (Назначен)
    REFERENCES Сотрудник(ИД_Сотрудника),
    CONSTRAINT FK_ЗадачиСотрудников_Сотрудник_Проверен FOREIGN KEY (Проверен)
    REFERENCES Сотрудник(ИД_Сотрудника) 
);

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

Для изменения ограничения, например, удалить требование NOT NULL или изменить ссылающийся столбец:

ALTER TABLE Сотрудник
DROP CONSTRAINT FK_Сотрудник_Отдел;

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

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

Здание 1 (🏢): Таблица Клиентов 
Здание 2 (🏬): Таблица Заказов

Определение внешнего ключа аналогично подключению специализированной линии:

ALTER TABLE 🏬 ADD CONSTRAINT 📞 "клиент_заказ" 
FOREIGN KEY (ид_клиента) REFERENCES 🏢(ид);

Результат коммуникации:

🏢📞➡️🏬: Линия "клиент_заказ" активирована!
# "Отдел заказов, приём: Получен идентификатор клиента. Мы связались с вами, верно?".

Сообщения о правилах общения:

🏢 – Обязательно используйте `ид_клиента`.
🏬 – Ответ дается только при распознавании `ид`.

Это символизирует чёткую и надёжную связь между двумя сущностями.

Преимущества именованных ограничений

Упрощение изменения структуры

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

Обеспечение целостности данных

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

Повышение эффективности

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

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

1. SQL FOREIGN KEY Constraint — W3Schools представляет подробное руководство по внешним ключам в SQL.
2. PostgreSQL: Documentation: 16: 5.4. Constraints — Официальная документация PostgreSQL по внешним ключам.
3. table_constraint (Transact-SQL) – SQL Server — Руководство Microsoft по ограничениям внешних ключей в SQL Server.
4. Foreign key constraint may cause cycles or multiple cascade paths? – Stack Overflow — Обсуждение на Stack Overflow возможных проблем ограничений внешних ключей в SQL Server.
5. MySQL :: MySQL 8.0 Reference Manual :: 15.1.20.5 FOREIGN KEY Constraints — Официальное руководство MySQL по внешним ключам.
6. constraint – Oracle FOREIGN KEY Constraints — Документация Oracle по ограничениям внешнего ключа.
7. How do I insert a row which contains a foreign key? – Database Administrators Stack Exchange — Обсуждение на Stack Exchange лучших практик в контексте именования ограничений внешнего ключа.