Условные внешние ключи MySQL: применение в разных моделях
Быстрый ответ
CREATE TRIGGER enforce_conditional_fk BEFORE INSERT ON child_table FOR EACH ROW
BEGIN
IF NEW.parent_id IS NOT NULL AND (SELECT COUNT(*) FROM parent_table WHERE id = NEW.parent_id AND condition = 'value') = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Условие не выполняется, вставка блокирована.';
END IF;
END;
Данный фрагмент кода создает триггер для таблицы child_table
. Этот триггер перед вставкой новой строки проверяет, выполнено ли условие в таблице parent_table
. Если условие не выполняется, вставка блокируется. Названия child_table
, parent_id
, parent_table
, condition
и value
– это переменные, которые необходимо заменить на реальные имена в вашей базе данных.
[AsideBanner]
Ограничения внешнего ключа и их преодоление
Когда внешний ключ затрагивает несколько таблиц, можно воспользоваться полиморфными ассоциациями. Они оперируют подходом супертаблицы – абстрактного слоя, содержащего общие атрибуты для всех возможных подтипов. Рассмотрим схему, в которой сущность ContentHolder
связана как с BlogPost
, так и с UserProfile
:
CREATE TABLE ContentHolder (
id INT PRIMARY KEY AUTO_INCREMENT,
content_type VARCHAR(255), // Тип содержимого.
content_id INT
);
CREATE TABLE BlogPost (
id INT PRIMARY KEY AUTO_INCREMENT,
pseudo_id INT, // Внешний ключ, ссылка на ContentHolder.
// Специфические поля для BlogPost
FOREIGN KEY (pseudo_id) REFERENCES ContentHolder(id)
);
CREATE TABLE UserProfile (
id INT PRIMARY KEY AUTO_INCREMENT,
pseudo_id INT, // Внешний ключ, ссылка на ContentHolder.
// Специфические поля для UserProfile
FOREIGN KEY (pseudo_id) REFERENCES ContentHolder(id)
);
В данной схеме каждая из подтаблиц сохраняет свою собственную структуру, но при этом ссылается на общую ContentHolder
, гарантируя целостность данных за счёт полиморфных ассоциаций.
Оптимизация подтипов таблиц
Подтипы таблиц позволяют без труда управлять условными ограничениями внешних ключей и являются надежным инструментом для поддержания нормализованной структуры данных.
Реализация условных связей с помощью вычисляемых столбцов
С выходом MySQL 5.7 появилась возможность использования создаваемых (вычисляемых) столбцов, что позволяет более гибко настраивать условные связи:
CREATE TABLE parent_table (
id INT PRIMARY KEY,
type VARCHAR(255),
// другие поля
);
CREATE TABLE child_table (
id INT PRIMARY KEY,
parent_id INT,
parent_type VARCHAR(255) GENERATED ALWAYS AS (
CASE
WHEN parent_id IS NOT NULL THEN (SELECT type FROM parent_table WHERE id = parent_id)
END
) STORED,
// другие поля
CONSTRAINT fk_conditional FOREIGN KEY (parent_id, parent_type) REFERENCES parent_table(id, type)
);
В данном случае столбец parent_type
формируется автоматически в зависимости от значения в parent_table
, что позволяет внешнему ключу учесть условия, заданные в обеих таблицах.
Визуализация
Условные ограничения внешних ключей в MySQL можно сравнить с системой приема гостей на вечеринку, где действует контроль приглашений:
Вечеринка (🎉): [Гость_А (🧑💼), Гость_Б (👩🏫), Гость_В (👨🚀)]
Приглашения (💌): [Приглашение_А (🔑), Приглашение_Б (🔒), Приглашение_В (🚪)]
Только гости с соответствующими приглашениями могут попасть на вечеринку:
Гость_А (🧑💼) + Приглашение_А (🔑) = Доступ разрешён! (✅)
Гость_Б (👩🏫) + Приглашение_В (🚪) = Доступ запрещён! (❌)
Точно так же устанавливаются и контролируются условия в базах данных.
Ограничения применения условных ограничений
Мы должны учитывать следующие аспекты при использовании условных связей:
Производительность
Использование подтипов таблиц и вычисляемых столбцов комплексирует SQL-конструкции запросов и может отражаться на производительности системы.
Каскадное удаление
Автоматическое каскадное удаление связанных записей при удалении родительской строки можно настроить следующим образом:
ALTER TABLE BlogPost
ADD CONSTRAINT fk_blogpost_cascade
FOREIGN KEY (pseudo_id)
REFERENCES ContentHolder(id)
ON DELETE CASCADE;
С такой настройкой строки в BlogPost
будут автоматически удаляться после удаления коррелирующей записи в ContentHolder
.
Обработка сложных условий
Сложные условия лучше обрабатывать на уровне приложения или с помощью хранимых процедур для повышения читаемости и упрощения структуры базы данных.
Полезные материалы
- MySQL :: Руководство по MySQL 8.0 :: 15.1.20.5 Ограничения внешнего ключа — официальная документация MySQL по ограничениям внешнего ключа.
- postgresql – Как вставить строку с внешним ключом? – Database Administrators Stack Exchange — вопросы и ответы о работе с внешними ключами в PostgreSQL, полезные для понимания подобных задач в MySQL.
- Как использовать внешние ключи MySQL для ускорения разработки баз данных — SitePoint — руководство по использованию внешних ключей в MySQL для ускорения работы над базами данных.