Условные внешние ключи 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 – это переменные, которые необходимо заменить на реальные имена в вашей базе данных.

Ограничения внешнего ключа и их преодоление

Когда внешний ключ затрагивает несколько таблиц, можно воспользоваться полиморфными ассоциациями. Они оперируют подходом супертаблицы – абстрактного слоя, содержащего общие атрибуты для всех возможных подтипов. Рассмотрим схему, в которой сущность 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.

Обработка сложных условий

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

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

1. MySQL :: Руководство по MySQL 8.0 :: 15.1.20.5 Ограничения внешнего ключа — официальная документация MySQL по ограничениям внешнего ключа.
2. postgresql – Как вставить строку с внешним ключом? – Database Administrators Stack Exchange — вопросы и ответы о работе с внешними ключами в PostgreSQL, полезные для понимания подобных задач в MySQL.
3. Как использовать внешние ключи MySQL для ускорения разработки баз данных — SitePoint — руководство по использованию внешних ключей в MySQL для ускорения работы над базами данных.