Добавить отношение внешнего ключа между двумя базами данных

У меня есть две таблицы в двух разных базах данных. В таблице 1 (в базе данных 1) есть столбец с именем column1, и это первичный ключ. Теперь в таблице 2 (в базе данных 2) есть столбец с именем column2, и я хочу добавить его в качестве внешнего ключа.

Я попытался добавить его, и он дал мне следующую ошибку:

Сообщение 1763, Уровень 16, Состояние 0, Строка 1
Перекрестные ссылки на внешние ключи не поддерживаются. Внешний ключ Database2.table2.

Сообщение 1750, уровень 16, состояние 0, строка 1
Не удалось создать ограничение. Смотрите предыдущие ошибки.

Как мне это сделать, так как таблицы находятся в разных базах данных.

Источник

7 ответов

Решение

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

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

Пример:

Create Trigger dbo.MyTableTrigger ON dbo.MyTable, After Insert, Update
As
Begin

   If NOT Exists(select PK from OtherDB.dbo.TableName where PK in (Select FK from inserted) BEGIN
      -- Handle the Referential Error Here
   END

END

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

Источник

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

Вот почему FK между базами данных не поддерживается.

Источник

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

CREATE FUNCTION dbo.fn_db2_schema2_tb_A
(@column1 INT) 
RETURNS BIT
AS
BEGIN
    DECLARE @exists bit = 0
    IF EXISTS (
      SELECT TOP 1 1 FROM DB2.SCHEMA2.tb_A 
      WHERE COLUMN_KEY_1 =  @COLUMN1
    ) BEGIN 
         SET @exists = 1 
      END;
      RETURN @exists
END
GO

ALTER TABLE db1.schema1.tb_S
  ADD CONSTRAINT CHK_S_key_col1_in_db2_schema2_tb_A
    CHECK(dbo.fn_db2_schema2_tb_A(key_col1) = 1)
Источник

По моему опыту, лучший способ справиться с этим, когда основной авторитетный источник информации для двух связанных таблиц должен находиться в двух отдельных базах данных, - это синхронизировать копию таблицы из основного местоположения во вторичное местоположение (используя T-SQL или SSIS с соответствующей проверкой ошибок - вы не можете обрезать и повторно заполнить таблицу, пока она имеет ссылку на внешний ключ, поэтому существует несколько способов скинуть кота при обновлении таблицы).

Затем добавьте традиционное отношение FK во втором расположении к таблице, которая фактически является копией только для чтения.

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

Источник

Достижение ссылочной целостности в базах данных — непростая задача.

Вот список часто используемых механизмов:

  • Клонирование и синхронизация . Ссылочные данные регулярно клонируются/вливаются в ссылочную базу данных. Это может быть удобно, если ссылочные данные редко изменяются. В итоге вы получаете две физические копии одних и тех же данных, и вам нужен надежный процесс для их синхронизации (например, с конвейером ETL).
  • Триггеры: изменения в ссылочных данных и ссылочных данных перехватываются триггерами SQL, которые обеспечивают ссылочную целостность. Однако триггеры могут работать медленно и не срабатывать при восстановлении базы данных. Не помешает запустить плановые проверки согласованности в рамках мониторинга операций. Доступ для записи к указанной базе данных требуется для установки и обслуживания триггера.
  • Проверка ограничений: SQL-Server предлагает пользовательские ограничения, которые гарантируют, что каждая строка удовлетворяет заданному условию. Эту функциональность можно использовать, написав определяемую пользователем функцию, которая проверяет наличие строки в данных, на которые ссылаются, а затем использует эту функцию в качестве предиката CHECK в таблице ссылок. Это не улавливает изменения в ссылочных данных. Это решение, специфичное для РСУБД, но оно работает за пределами серверов (например, с использованием связанных серверов). Это хороший выбор для ссылки на глобально уникальные идентификаторы, такие как коды статей в системе ERP компании, которые никогда не удаляются и не переназначаются.
  • Переосмыслите архитектуру базы данных: когда все вышеперечисленные механизмы неудовлетворительны, несколько баз данных могут быть объединены в одну базу данных. Исходные имена баз данных могут стать именами схем, что позволяет эффективно группировать объекты базы данных.
Источник

Короткий ответ: SQL Server (начиная с SQL 2008) не поддерживает внешние ключи базы данных, как говорится в сообщении об ошибке.

Хотя у вас не может быть декларативной ссылочной целостности (FK), вы можете достичь той же цели с помощью триггеров. Это немного менее надежно, потому что в логике, которую вы пишете, могут быть ошибки, но она точно так же поможет вам.

См. Документацию по SQL @ http://msdn.microsoft.com/en-us/library/aa258254%28v=sql.80%29.aspx каком состоянии:

Триггеры часто используются для обеспечения соблюдения бизнес-правил и целостности данных. SQL Server обеспечивает декларативную ссылочную целостность (DRI) посредством операторов создания таблиц (ALTER TABLE и CREATE TABLE); однако DRI не обеспечивает ссылочную целостность базы данных. Чтобы обеспечить ссылочную целостность (правила, касающиеся отношений между первичным и внешним ключами таблиц), используйте ограничения первичного и внешнего ключей (ключевые слова PRIMARY KEY и FOREIGN KEY для ALTER TABLE и CREATE TABLE). Если для таблицы триггеров существуют ограничения, они проверяются после выполнения триггера INSTEAD OF и до выполнения триггера AFTER. Если ограничения нарушаются, действия триггера INSTEAD OF откатываются, а триггер AFTER не выполняется (срабатывает).

Кроме того, в SQLTeam обсуждается вопрос "хорошо" - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31135

Источник

Как говорится в сообщении об ошибке, это не поддерживается на сервере SQL. Единственный способ обеспечить ссылочную целостность - это работать с триггерами.

Источник
Другие вопросы по тегам