Создание внешнего ключа между таблицами в разных БД

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

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

Рассмотрим пример использования внешнего ключа в SQL Server между двумя базами данных:

ALTER TABLE Database1.dbo.Table1
ADD CONSTRAINT FK_Table1_Table2
FOREIGN KEY (Column1) REFERENCES Database2.dbo.Table2(Column1);

Если же необходимо найти решение, совместимое с различными СУБД, рекомендуется использовать триггеры:

CREATE TRIGGER CheckForeignKey
ON Database1.dbo.Table1
FOR INSERT, UPDATE 
AS
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM Database2.dbo.Table2 
        WHERE Column1 = INSERTED.Column1
    )
    RAISERROR ('Нарушение внешнего ключа', 16, 1);
    ROLLBACK;
END;

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

Выбор: плюсы и минусы внешних ключей между базами

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

Переодоление отказов: борьба с ограничениями СУБД

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

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

Рассмотрим две базы данных, Database 1 🏰1 и Database 2 🏰2, со следующими схемами:

🏰1: [ID гражданина, Имя, Профессия]
🏰2: [ID собственности, Местоположение, ID владельца-гражданина]

Установим связь 🌉 между ID владельца-гражданина в 🏰2 и ID гражданина в 🏰1:

🏰1 ↔️🌉↔️ 🏰2

Мост 🌉 представляет собой внешний ключ:

🌉: ID владельца-гражданина в 🏰2 *должен соответствовать* ID гражданина в 🏰1

Таким образом, каждый владелец недвижимости в базе данных Database 2 🏰2 подтвержден в качестве гражданина в базе данных Database 1 🏰1. 🛂✅

Кризисные ситуации: когда использовать триггеры для обеспечения ссылочной целостности?

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

Альтернативный план: варианты замены внешнего ключа между базами

Если триггеры или межбазовые ссылки кажутся слишком сложными, требуется пересмотреть структуру базы данных:

• Слияние баз данных: Можно переместить таблицы в одну базу данных, чтобы избежать межбазовых отношений.
• Доверие к приложениям: Можно обеспечить целостность данных на уровне ПО, но этот вариант менее надежен и увеличивает ответственность разработчиков.
• Репликация: Синхронизация данных между базами с помощью репликации или определения изменений, что увеличивает вычислительную нагрузку.

Управление записями-сиротами

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

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

1. MySQL :: Руководство по MySQL 8.0 :: 13.1.20.5 Ограничения FOREIGN KEY — Инструкция по использованию ограничений FOREIGN KEY в MySQL.
2. PostgreSQL: Документация: 16: F.38. postgres_fdw — доступ к данным на внешних серверах PostgreSQL — Информация о доступе к данным на внешних PostgreSQL серверах.
3. sql – Выбор символа '(одинарная кавычка) – Stack Overflow — Обсуждение методов управления межбазовыми отношениями на Stack Overflow.
4. SQL Essential Training Online Class | LinkedIn Learning, бывший Lynda.com — Образовательный курс по основам SQL и принципам работы с отношениями в базах данных.