Главная » Microsoft SQL Server, Базы данных » Поддержка сложной ссылочной целостности

0

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

Можно сказать, что перед тем как пытаться создавать сложный триггер поддержания ссылочной целостности, имеет смысл попытаться решить задачу стандартными и более простыми методами. Программный код триггера поддержания ссылочной целостности будет содержать серию объединений первичных и вторичных таблиц, чтобы найти такие значения внешнего ключа таблицы Inserted, которые не имеют соответствий в первичной таблице. В рассматриваемом ниже примере таблица ТаЫеВ имеет внешний ключ, указывающий на таблицу ТааЫеА. Отмечу, что рассматриваемый программный код не привязан ни к какой конкретной базе данных:

CREATE TRIGGER RICheck ON Tour AFTER INSERT, UPDATE AS

SET NoCount ON IF Exists(SELECT *

FROM Inserted

LEFT OUTER JOIN BaseCamp ON Inserted.BaseCampID = BaseCamp.BaseCampID WHERE BaseCamp.BaseCampID IS NULL)

BEGIN

RAISERROR

(‘Несоответствующий внешний ключ: Tour.BaseCampID’ , 16, 1)

ROLLBACK TRANSACTION RETURN END

Далее мы попробуем назначить тур Amazon Trek базовому лагерю с идентификатором 99. Триггер поддержания ссылочной целостности блокирует обновление данных:

UPDATE Tour

SET BaseCampID = 99 WHERE TourID = 1

Как и ожидалось, результатом будет ошибка:

Несоответствующий внешний ключ: Tour.BaseCampID

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

Примером сложной ссылочной целостности может послужить система MRP П, с которой мне приходилось работать. Эта система должна была заполнять строки накладной либо из счета-фактуры, либо из складской таблицы. Вначале мы пытались решить задачу с помощью двух внешних ключей таблицы Allocation: один из них указывал на таблицу OrderDetail (строки накладной) и обслуживался требованиями таблицы Product (товары). Второй ключ указывал на источник заполнения накладной и мог быть либо глобальным универсальным идентификатором строки (GUID) счета-фактуры, либо складским идентификатором GUID. Так как второй внешний ключ не мог однозначно указывать на строку конкретной таблицы, ограничение стандартного внешнего ключа не способно было справиться с задачей.

Чтобы реализовать поддержание такой сложной ссылочной целостности, в таблице Allocation был создан триггер. Этот триггер проверял правильность идентификатора строки счета-фактуры или складской позиции. Это было реализовано с помощью запроса различия, сравнивающего все строки таблицы Inserted с полями Source ID таблиц Inventory и PurchaseOrderDetail:

CREATE TRIGGER AllocationCheck ON Allocation AFTER INSERT, UPDATE AS

SET NoCount ON

– Проверка некорректности складской позиции IF Exists(SELECT *

FROM Inserted I LEFT OUTER JOIN Inventoryltem ON I.SourcelD = Inventoryltem.InventoryltemID LEFT OUTER JOIN PurchaseOrderDetail ON I.SourcelD = PurchaseOrderDetail.PODID WHERE Inventory.InventorylD IS NULL

AND PurchaseOrderDetail.PODID IS NULL)

BEGIN

RAISERROR (‘Неверный источник товара’, 16, 1)

ROLLBACK TRANSACTION RETURN END

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

ALTER TABLE Allocation

ADD CONSTRAINT AllocationSourceExclusive CHECK

(PurchaseOrderID IS NULL AND InventorylD IS NOT NULL)

OR

(PurchaseOrderID IS NOT NULL AND InventorylD IS NULL)

Выбор одного из методов поддержания сложной ссылочной целостности зависит от извлечения корректной информации и привычного стиля работы самого разработчика. Оба метода требуют использования левых внешних объединений и функции coalesce ().

Источник: Нильсен, Пол. Microsoft SQL Server 2005. Библия пользователя. : Пер. с англ. — М. : ООО “И.Д. Вильямс”, 2008. — 1232 с. : ил. — Парал. тит. англ.

По теме:

  • Комментарии